Use EXTRACT
and the UNIX-Timestamp
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2011-05-17 10:40:28.876944') * 1000;
would give
1305621628876.94
Multiply it by 1000
to turn it into milliseconds. You can then convert it to whatever you want (decimal would be a good choice). Don't forget to keep the timezone in mind. JackPDouglas has such an example in his answer. Here is an excerpt from his answer (created
being the column with your timetamp) that illustrates how to work with timezones:
SELECT EXTRACT(EPOCH FROM created AT TIME ZONE 'UTC') FROM my_table;
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
Best Answer
You can't meaningfully change the time relatively in either direction (forward, or backwards) without potentially impacting the date. Timestamp is a hybrid of both date and time realizing that the two are inseparable in any real world instance. You can not go from
2017-07-01 00:00:00
to2017-07-01 17:00:00
in a safe fashion through incrementing the time. If you try it, you're making an assumption about what the date was. However, you can set the time portion of a timestamp, dropping the date portion entirely withdate_trunc
. In this method, you ensure what the time portion is set as (or at least with far more certainty). So long as you don't specifically force a explicit rollover (like adding 25 hours) when you're setting it, you should be good. I see two questions here:Here is some code showing both approaches
Aside from datetime math, think
$time=17
, vs$datetime+=17
. One of them is far safer and less likely to create roll over. Do you want to increment the current column at whatever it may be by 17 hours, or set it to17:00:00
on that day?