How to return correct local time in Postgres

Question

select current_time at time zone 'GMT-2'

returns

"11:54:40.22045+02"

but correct local time in Windows is one hour different:

12:54

How to get correct local time ?

Using

"PostgreSQL 9.6.0, compiled by Visual C++ build 1800, 32-bit"

with standard postgresql.conf file in Windows 10 Same issue occurs also in ealier Postgres and in earlier windows.

Server time in Windows is correct.

Daylight saving time was changed by one hour a week ago. Maybe postgres didnt recognized it.

Might be this can help you: postgresql.org/docs/9.1/static/… — Kinchit Dalwani
– Kinchit Dalwani, Commented Mar 29, 2017 at 10:05
There was a DST change a few days ago. Most probably you are not in 'GMT-2' anymore, Dorothy. (Sorry for the pun. Have you tried to use your named time-zone instead?) — pozs
– pozs, Commented Mar 29, 2017 at 10:16
@VaoTsun select current_time returns "10:46:53.975868+00" which is 3 hours different from windows time — Andrus
– Andrus, Commented Mar 29, 2017 at 10:47
@pozs select current_time at time zone 'Europe/Tallinn' returns correct time. Is it OK to use this in all postgres version starting from 8.1 ? — Andrus
– Andrus, Commented Mar 29, 2017 at 10:51

Laurenz Albe · Accepted Answer · 2017-03-29 10:30:03Z

4

Don't use time with time zone, it is a useless data type.

See the documentation:

The type time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness. In most cases, a combination of date, time, timestamp without time zone, and timestamp with time zone should provide a complete range of date/time functionality required by any application.

Use localtime to get the current time at your current session time zone (defined by the TimeZone parameter).

answered Mar 29, 2017 at 10:30

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Andrus Over a year ago

localtime at time zone 'GMT-2' still returns 12:42... but correct time is 13:42 . How to get correct local time in windows default or hard coded Helsinki/Tallinn timezone without changing postgresql.conf ? localtime returns 10:45 which is even more wronger

Collectives™ on Stack Overflow

How to return correct local time in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related