CODEWITHSUNDEEP
postgresql
Ben
Ben

Reputation: 149

Handling oddly-formatted timestamp in Postgres?

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?

Upvotes: 2

Views: 954

Answers (1)

Ben
Ben

Reputation: 149

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.

Upvotes: 1

Related Questions