CODEWITHSUNDEEP
postgresql
Bruno
Bruno

Reputation: 23

Converting UTC time in local time in PostgreSQL 8.3

I run a Postgres 8.3 database where times seem to be stored in UTC without time zone. I am trying to display in local time but not with '+01' suffix :

With select scheduled_start_ts I get : 2014-01-20 05:01:35.663

With select scheduled_start_ts at time zone 'MET' : 2014-01-20 05:01:35.663+01

I would like to get "2014-01-20 06:01:35.663" which is in local time.

The database I am using cannot be modified and I am not allowed to modify how data are stored.

Upvotes: 2

Views: 1272

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324295

If you want to format times, use the to_char function. See formatting functions in the docs.

regress=> SELECT to_char( 
   (TIMESTAMP '2014-01-20 05:01:35.663' AT TIME ZONE 'UTC') 
   AT TIME ZONE 'MET',
   'YYYY-MM-DD HH:MI:SS'
   );

       to_char       
---------------------
 2014-01-20 06:01:35
(1 row)

The (TIMESTAMP 'xxx' AT TIME ZONE 'UTC') gets me a timestamptz with the correct time, by re-interpreting the TIMESTAMP as being in UTC. The second AT TIME STAMP instead converts the timestamptz into a timestamp in timezone MET. This then gets formatted.

Whatever the SQL standards committe were smoking when they designed this, I never, ever, ever want to be anywhere near it.

Upvotes: 1

Related Questions