Timestamp in another time zone relative to the current date

From PostgreSQL wiki
Jump to navigationJump to search

Snippets

Timestamp in another time zone, relative to the current date

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing


This page has examples for producing the timestamp value for "9AM, Yesterday, in Los Angeles".

Timestamp with time zone, relative to the current date in the target time zone

=# select now();
             now              
-------------------------------
 2014-07-05 02:58:03.598569+02
(1 row)
=# select (date_trunc('day', now() AT TIME ZONE 'America/Los_Angeles' - interval '1 day') + time '09:00:00') AT TIME ZONE 'America/Los_Angeles';
       timezone        
------------------------
 2014-07-03 18:00:00+02
(1 row)


Timestamp without time zone, relative to the current date in the target time zone

=# select now();
             now              
-------------------------------
 2014-07-05 02:58:03.598569+02
(1 row)
=# select (date_trunc('day', now() AT TIME ZONE 'America/Los_Angeles' - interval '1 day') + time '09:00:00');
     ?column?       
---------------------
 2014-07-03 09:00:00
(1 row)