Skip to content

PostgreSQL

Jump to bottom
Charlie edited this page Jul 11, 2017 · 3 revisions

Dates and Times

  • See the current time TimeZone: SHOW TIMEZONE;

  • timestamp with time zone

    • Internally stored value is always in UTC.
    • Input values are converted to UTC before being stored. If no time zone is present in the string, the TimeZone parameter is used.

  • timestamp without time zone AT TIME ZONE treats the timestamp as if it was located in the specified time zone. It returns a timestamp with time zone and no conversion is made.

  • timestamp with time zone AT TIME ZONE converts the timestamp into the specified time zone. It returns a timestamp without time zone.

For example:

SHOW TIMEZONE;
Result: America/New_York

SELECT '2017-07-11 18:30:00'::TIMESTAMP WITHOUT TIME ZONE;
Result: 2017-07-11 18:30:00.00

SELECT '2017-07-11 18:30:00'::TIMESTAMP WITHOUT TIME ZONE AT TIME ZONE 'MST';
Result: 2017-07-11 21:30:00.00

The second example takes the timestamp, assumes that the time is MST, then converts it to EST for display.

Useful Links

Clone this wiki locally