xxxxxxxxxx
CREATE TABLE timestamp_demo (
ts TIMESTAMP,
tstz TIMESTAMPTZ
);
Code language: SQL (Structured Query Language) (sql)
xxxxxxxxxx
INSERT INTO timestamp_demo (ts, tstz)
VALUES('2016-06-22 19:10:25-07','2016-06-22 19:10:25-07');
Code language: SQL (Structured Query Language) (sql)
xxxxxxxxxx
select '2011-03-27 00:59:00.0+00'::timestamptz::timestamp::timestamptz
, '2011-03-27 01:00:00.0+00'::timestamptz::timestamp::timestamptz;
/*
|timestamptz |timestamptz |
|:---------------------|:---------------------|
|2011-03-27 00:59:00+00|2011-03-27 02:00:00+01|
*/
xxxxxxxxxx
set timezone to 0;
values(1, '1970-01-01 00:00:00+00'::timestamp::timestamptz)
, (2, '1970-01-01 00:00:00+02'::timestamp::timestamptz);
/*
|column1|column2 |
|------:|:---------------------|
| 1|1970-01-01 00:00:00+00|
| 2|1970-01-01 00:00:00+00|
*/
xxxxxxxxxx
CREATE TABLE timestamp_demo (
ts TIMESTAMP,
tstz TIMESTAMPTZ
);
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-timestamp/
xxxxxxxxxx
ts | tstz
---------------------+------------------------
2016-06-22 19:10:25 | 2016-06-22 19:10:25-07
(1 row)
Code language: SQL (Structured Query Language) (sql)
xxxxxxxxxx
select now(),
extract(epoch from now()), -- correct
extract(epoch from now() at time zone 'utc'), -- incorrect
extract(epoch from now() at time zone 'utc' at time zone 'utc'); -- correct
now | date_part | date_part | date_part
-------------------------------+------------------+------------------+------------------
2014-10-14 10:19:23.726908+02 | 1413274763.72691 | 1413267563.72691 | 1413274763.72691
(1 row)
xxxxxxxxxx
ts | tstz
---------------------+------------------------
2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
(1 row)
Code language: SQL (Structured Query Language) (sql)