Thread: selecting timestamp
What is the best way of selecting current timestamp in UTC?
SELECT
CURRENT_TIMESTAMP as ct1
,timezone('UTC',CURRENT_
,timezone('utc',now()) as ct3
,CURRENT_TIMESTAMP at time zone 'UTC' as ct4
,NOW() at time zone 'utc' as ct5
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
Well the above would depend on your database having its time zone
set to UTC.
> ,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
The rest would do the job. The question becomes where are you planning
on calling these and what is tine you are looking for? See the below for
more information:
https://www.postgresql.org/docs/10/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
Basically the functions act differently in transactions, which will
affect the time returned.
--
Adrian Klaver
adrian.klaver@aklaver.com
What is the best way of selecting current timestamp in UTC?
You
haven't
defined
criteria upon which to judge - and the list below is not exhaustive (but sufficiently so)
SELECTCURRENT_TIMESTAMP as ct1
standard conforming, assumes server configured for UTC
,timezone('UTC',CURRENT_TIMESTAMP) as ct2 ,timezone('utc',now()) as ct3
non-standard, personally non-obvious (the function looks like an implementation detail that should be avoided)
,CURRENT_TIMESTAMP at time zone 'UTC' as ct4
This -
standard conforming and doesn't require assumptions about the calling environment
,NOW() at time zone 'utc' as ct5
non-standard but frequently used; no semantic different compared to the previous entry
David J.
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
Attachment
Thank you everyone for the help.
Sorry there wasn't much detail given originally.
CURRENT_TIMESTAMP at time zone 'UTC' as ct4Seems to be working well.
Thanks,
Chris
On 02/27/2018 01:26 PM, David G. Johnston wrote:
What is the best way of selecting current timestamp in UTC?You haven'tdefinedcriteria upon which to judge - and the list below is not exhaustive (but sufficiently so)SELECTCURRENT_TIMESTAMP as ct1standard conforming, assumes server configured for UTC,timezone('UTC',CURRENT_TIMESTAMP) as ct2 ,timezone('utc',now()) as ct3non-standard, personally non-obvious (the function looks like an implementation detail that should be avoided),CURRENT_TIMESTAMP at time zone 'UTC' as ct4This -standard conforming and doesn't require assumptions about the calling environment,NOW() at time zone 'utc' as ct5non-standard but frequently used; no semantic different compared to the previous entryDavid J.