In postgres, timestamp with time zone
can be abbreviated as timestamptz
, and timestamp without time zone
as timestamp
. I will use the shorter type names for simplicity.
Getting the Unix timestamp from a postgres timestamptz
like now()
is simple, as you say, just:
select extract(epoch from now());
That's really all you need to know about getting the absolute time from anything of type timestamptz
, including now()
.
Things only get complicated when you have a timestamp
field.
When you put timestamptz
data like now()
into that field, it will first be converted to a particular timezone (either explicitly with at time zone
or by converting to the session timezone) and the timezone information is discarded. It no longer refers to an absolute time. This is why you don't usually want to store timestamps as timestamp
and would normally use timestamptz
— maybe a film gets released at 6pm on a particular date in every timezone, that's the kind of use case.
If you only ever work in a single time zone you might get away with (mis)using timestamp
. Conversion back to timestamptz
is clever enough to cope with DST, and the timestamps are assumed, for conversion purposes, to be in the current time zone. Here's an example for GMT/BST:
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|
*/
DBFiddle
But, note the following confusing behaviour:
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|
*/
DBFiddle
This is because:
PostgreSQL never examines the content of a literal string before determining its type, and therefore will treat both […] as timestamp without time zone. To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type…In a literal that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication
There is a manual page on Time and Date functions.
The best way to compare date and time and get minutes back is probably to use one of the following:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)
Note that while TIMEDIFF
works with times, TIMESTAMPDIFF
requires a date component - and the values are switched around for TIMESTAMPDIFF
.
You can return hours with either of these:
SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)
Note that when using DATEDIFF
, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).
To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:
SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)
This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.
Best Answer
That information from the 9.6 documentation is wrong. Timestamps are stored as microseconds since 2000-01-01 00:00:00.
See
include/datatype/timestamp.h
:Back in 9.6, there was also the option to store timestamps as floating point values, but that has been removed since.
That should answer your first question.
The reason why 2000 was picked probably stems from the time when you could store timestamps as floating point values. Here, the precision got better for values close to the epoch, and it made sense to pick something more future-proof than 1970 (mind you, that was before 2000).
Subtracting a constant in order to calculate the Unix epoch is no great effort.