PostgreSQL Migration: DateTime showing as "Thu Jan 01 00:00:00 1970"

RT Users
1

Good Evening BP Community!

I’ve recently undertaken a migration from MariaDB to Postgres and everything is running very smoothly. No problems at all, except for a small cosmetic issue.

All of the datetime values in the UI that should show up as “Not set” are instead showing up as “Thu Jan 01 00:00:00 1970” which I believe means that the code is expecting “0” seconds in UnixTime but postgres returns the string instead of the unixtime value? I could be off on that but it’s the only hypothesis I could come up with at this hour…

Has anyone else done a migration to Postgres and experienced the same?

Also if anyone needs help getting through the earlier stages of a migration to Postgres, I’m happy to help out… as I have a perfectly working instance, with this small UI artifact aside.

Thank you for reading
-g

2

Hi, @geraldsnm , I stated with PG so I’ve not any issue related to this. I’ve checked some time fields in DB and the default value is NULL. Test updating a time field in a single record. If it works, update all date fields to NULL where needed.
I hope this helps.

3

That explains it… I tried setting one field manually and I see “Not set”

update tickets set starts=null where starts='1970-01-01 00:00:00-05' ;

… But that’s a lot of columns to convert… I’m going to try to cast the date field when running pgloader… Something like this may work

LOAD DATABASE
FROM mysql://root:root@mysql57/database1
INTO postgresql://postgres@localhost/database2
CAST type datetime to timestamp drop default drop not null using zero-dates-to-null;
4

Maybe rt-validator - RT 5.0.6 Documentation - Best Practical can help solving this issue. I ran this script yesterday for the firs time. It solved some inconsistencies, values, etc.

5

@geraldsnm did you end up figuring out the Postgresql migration using the rt- serializer? How did you get pass the zero date issue?

6

Honestly no I never got rt-serializer to work.. I used pgloader which is meant for converting mysql (or other DBs) to postgres…

Sorry for the late response, but here’s my pgloader.ini for anyone else that finds this post in the future:

LOAD DATABASE
FROM mysql://myuser:mypass@ipaddr/rt5
INTO postgresql://myuser:mypass@ipaddr/rt5
WITH prefetch rows = 10000
CAST type datetime to timestamp drop default drop not null using zero-dates-to-null