'get time for timezone

I'm hurting my head again this :

On postgresql, I would like to get the local time for a given timezone.

So, at 15:45 GMT, I want 16:45 for +01:00, but I can't get the good anwser :

SQL Fiddle

Query 1:

select current_timestamp at time zone 'GMT' as time_local_gmt

Results:

|              time_local_gmt |
|-----------------------------|
| 2018-01-26T15:45:10.871659Z |

This is OK.

Query 2:

select current_timestamp at time zone '+01:00' as time_local_paris

Results:

|            time_local_paris |
|-----------------------------|
| 2018-01-26T14:45:10.871659Z |

This is totally wrong, seem like it's -01:00 instead of +01:00

Edit : See the valid answer here : https://stackoverflow.com/a/48707297/5546267

sqlpostgresqltimezone


Solution 1:[1]

Ok, finally found how to ! 

SELECT
    current_timestamp 
        AT TIME ZONE 'GMT' 
        AT TIME ZONE '+01:00' 
            AS time_local_paris_right;

The timestamp is UTC without TZ by default, you force it as a GMT one, and then the second AT convert it with the right offset to give you the local time for the specified time zone.

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

Query 2:

select current_timestamp at time zone 'GMT' as time_local_gmt

Results:

|              time_local_gmt |
|-----------------------------|
| 2018-02-09T13:44:56.824107Z |

Query 3:

select current_timestamp at time zone '+01:00' as time_local_paris_wrong

Results:

|      time_local_paris_wrong |
|-----------------------------|
| 2018-02-09T12:44:56.824107Z |

Query 4:

select current_timestamp at time zone 'GMT' at time zone '+01:00' as time_local_paris_right

Results:

|      time_local_paris_right |
|-----------------------------|
| 2018-02-09T14:44:56.824107Z |

Solution 2:[2]

This worked for me.

select current_timestamp at time zone 'UTC+1';

Gave me the following result.

2018-01-26T17:00:58.773039Z

There is also a list of timezone names.

Here is an excerpt from the PostgreSQL 9.6 documentation regarding timezone names.

The view pg_timezone_names provides a list of time zone names that are recognized by SET TIMEZONE, along with their associated abbreviations, UTC offsets, and daylight-savings status.

Basically, the following query will give you the current time in Paris.

SELECT current_timestamp AT TIME ZONE 'Europe/Paris';

Good Luck!

Solution 3:[3]

For completeness (even if @Avi Abrami's answer should be what you're searching for) let's take a look at the datetime operators in the docs.

One can use the INTERVAL keyword to add hours to the stored value:

SELECT current_timestamp AT TIME ZONE INTERVAL '+02:00' AS plus_two;

Which then results in 

2018-01-26T17:45:10.871659Z

(when GMT time is 2018-01-26T15:45:10.871659Z)

Section 9.9.3 AT_TIME_ZONE mentions my use of INTERVAL without any preceeding operator:

In these expressions, the desired time zone zone can be specified either as a text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00'). In the text case, a time zone name can be specified in any of the ways described in Section 8.5.3.

Solution 4:[4]

The documentation says:

Another issue to keep in mind is that in POSIX time zone names, positive offsets are used for locations west of Greenwich. Everywhere else, PostgreSQL follows the ISO-8601 convention that positive timezone offsets are east of Greenwich.

I guess that is your problem.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3
Solution 4 Laurenz Albe

Related Questions