Re: selecting timestamp

On 02/27/2018 12:16 PM, chris wrote:
>
> What is the best way of selecting current timestamp in UTC? 
>
> SELECT
> CURRENT_TIMESTAMP   as ct1
> ,timezone('UTC',CURRENT_TIMESTAMP) as ct2
> ,timezone('utc',now()) as ct3
> ,CURRENT_TIMESTAMP at time zone 'UTC' as ct4
> ,NOW() at time zone 'utc' as ct5

Depends on whether you want a volatile or stable answer:

\x
SELECT clock_timestamp(),
       clock_timestamp() AT TIME ZONE 'UTC',
       now(),
       now() AT TIME ZONE 'UTC'
FROM generate_series(1,3);
-[ RECORD 1 ]---+------------------------------
clock_timestamp | 2018-02-27 12:30:10.46699-08
timezone        | 2018-02-27 20:30:10.466991
now             | 2018-02-27 12:30:10.466692-08
timezone        | 2018-02-27 20:30:10.466692
-[ RECORD 2 ]---+------------------------------
clock_timestamp | 2018-02-27 12:30:10.467017-08
timezone        | 2018-02-27 20:30:10.467017
now             | 2018-02-27 12:30:10.466692-08
timezone        | 2018-02-27 20:30:10.466692
-[ RECORD 3 ]---+------------------------------
clock_timestamp | 2018-02-27 12:30:10.467023-08
timezone        | 2018-02-27 20:30:10.467023
now             | 2018-02-27 12:30:10.466692-08
timezone        | 2018-02-27 20:30:10.466692

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development