0

I have a CSV file which contains birth date column. I'm importing into my PostgreSQL database, using Adobe Campaign tool (formerly called as neolane).

In my tool I've provided the data type of birth date column to be mm/dd/yyyy hh:mm:ss

Dates like 9/20/1942 12:00:00 AM and newer when stored to database are being saved correctly e.g. 1942-09-20 00:00:00+06:30. Not sure how fraction of second is being calculated. If you can comment on that.

Then older values (all older than 1941) such as 10/1/1941 12:00:00 AM are being stored in database as 1941-09-30 23:23:20+05:53:20 which is a day older the one mentioned in birth date column.

Not sure if this is a problem related to PostgreSQL or with the product.

Vipul
  • 2,023
  • 2
  • 15
  • 12
  • The part after the "+" is a timezone offset. – Andrew Morton May 16 '16 at 15:25
  • Thank you Andrew. The point that I'm not understanding is why is the database picking +6:30 for some values and for others it is +5:30. As you can see, in the source CSV there is no mention of timezone – Vipul May 16 '16 at 15:29
  • It could be the tool you are using to import the data is not behaving as you expect. A timezone offset of +05:53:20 is somewhat unlikely. Check the documentation. – Andrew Morton May 16 '16 at 15:33

1 Answers1

1

Firstly you say that you are trying to import dates but are clearly importing timestamps. Dates do not have hours, minutes or seconds.

Do not use timestamps when you want a date.

Secondly, you aren't providing a timezone for these values, but are storing them as "timestamp with time zone".

I'm not sure what your local timezone is, but if we try Calcutta then it looks like we get similar results. Timezone offsets tend to get a little odd as you go further back in time. In lots of places it wasn't until the arrival of major train travel and the telegraph that there was much urgency to standardize timezones. That could take a long time depending on local politics.

So - if you can possibly do so, use dates.

If not, specify the timezone you mean when saving the data and also do not use midnight, use 10AM or noon or similar for the time. Otherwise timezone changes will (quite correctly) affect the date displayed.

There is a discussion of date and time handling in the manuals. The "Olson" timezone history database is mentioned there and provides some interesting reading.

Richard Huxton
  • 21,516
  • 3
  • 39
  • 51