Handling oddly-formatted timestamp in Postgres?

Question

I have about 32 million tuples of data of the format:

2012-02-22T16:46:28.9670320+00:00

I have been told that the +00:00 indicates an hour:minute timezone offset, but also that Postgres only takes in hour offset (even in decimals), not the minute. So would I have to process the data in order to remove the last :00 from every tuple and read the data in as timestamps? I would like to avoid pre-processing the data file, but if Postgres will not accept the values otherwise, then I will do so.

In addition, the precision specified in the given data is 7 decimal places in the seconds part, whereas Postgres timestamp data type allows for maximum 6 decimal place precision (milliseconds). Would I have to modify the 7 decimal place precision to 6 in order to allow Postgres to read the records in, or will Postgres automatically convert the 7 to 6 as it reads the tuples?

How are you going to be pushing your data file into postgres? I expect that you'll have to ensure the formatting is correct if you simply want to load up the data file. If you're using some sort of script to handle the database writes, the script should be written to also convert the data into the necessary format. A type-aware interface (though overkill) like SQLAlchemy can also do more complex conversions. — Chintalagiri Shashank
– Chintalagiri Shashank, Commented Jul 10, 2016 at 19:03
I'm trying to use copy (table) from program (cut fields, file). I have seen that this is possible: pgsql=# SELECT '2016-07-10 20:12:21.8372949999+02:30'::timestamp with time zone AS ts; ts ------------------------------- 2016-07-10 17:42:21.837295+00 (1 row) so it seems that Postgres will automatically converts these cases, so if it does it here, I believe it should also convert the tuples when reading them from the file. — Ben
– Ben, Commented Jul 10, 2016 at 19:19

Ben · Accepted Answer · 2016-07-10 19:49:40Z

1

pgsql=# SELECT '2016-07-10 20:12:21.8372949999+02:30'::timestamp with time zone AS ts;

ts------------------------------- 2016-07-10 17:42:21.837295+00 (1 row)

It seems that at least in PostgreSQL 9.4 and up (maybe earlier), minutes timezone offset handling is not documented, but does get processed properly if used. In a similar vein, if I try to read in a timestamp that has 7 decimal place precision in the seconds, then it will automatically convert that to 6 decimal place (microsecond) precision instead.

edited Jul 10, 2016 at 19:49

answered Jul 10, 2016 at 19:13

Ben

1491 gold badge2 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jonathan Leffler Over a year ago

Did you explore what PostgreSQL does when the time zone offset has non-zero minutes — something like +05:30 (India) or +05:45 (Nepal), or -03:30 (Newfoundland)?

Ben Over a year ago

Yeah see my comment reply to Chintalagiri Shashank

Jonathan Leffler Over a year ago

It would probably be better if the information was moved out of a comment, either into your answer or into your question. One major reason for doing that is that you can format the information so it is more easily read. But you're right — you seem to have covered the point. (Is there a region of the world that uses +02:30 as a time zone offset?)

Ben Over a year ago

I have no idea what region uses this, but the +00:00 is specified in my data, so I have to address it. I will move the relevant part of my comment to my answer. Sorry, I'm not too practiced in using this site yet.

Collectives™ on Stack Overflow

Handling oddly-formatted timestamp in Postgres?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related