Timestamp in another time zone relative to the current date

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)