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.