SELECT EXTRACT(EPOCH FROM TIMESTAMP '2003-01-01 11:23:44');
Yeah, but I think Michael's question had to do with going the other
way (numeric to timestamp).
Sorry,
SELECT EXTRACT(TIMESTAMP FROM EPOCH '123412341234');
Chris
---(end of broadcast)---
On Jul 16, 2004, at 3:08 PM, Christopher Kings-Lynne wrote:
SELECT EXTRACT(TIMESTAMP FROM EPOCH '123412341234');
That's a really interesting idea! Makes for a much more consistent
syntax for our other functions. ISTM this might require a native EPOCH
datatype. I wouldn't want to encourage people
Hello all.
In IRC, one of the questions we get from time to time is how to convert
UNIX epoch to PostgreSQL timestamp. Users are often surprised there
isn't a builtin cast or function that does this.
I've put together two simple SQL functions that accomplish this. I
think they would make the
Michael Glaesemann [EMAIL PROTECTED] writes:
create or replace function epoch_to_timestamp(
integer
) returns timestamp(0)
language sql as '
SELECT ''epoch''::timestamp + $1 * ''1 second''::interval;
';
This is in fact wrong, unless you live in the British Isles:
This is in fact wrong, unless you live in the British Isles: the
result will be off by your timezone displacement from UTC. Correct
is to use timestamptz not timestamp.
As an example: timestamp 1089953023 equates to Fri Jul 16 2004, 00:43:43 EDT
according to strftime() on my machine (I live in US
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
I would actually prefer this syntax:
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2003-01-01 11:23:44');
Yeah, but I think Michael's question had to do with going the other
way (numeric to timestamp).
regards, tom lane
On Jul 16, 2004, at 1:55 PM, Tom Lane wrote:
This is in fact wrong, unless you live in the British Isles: the
result will be off by your timezone displacement from UTC. Correct
is to use timestamptz not timestamp.
Thanks. Timestamps and time zones are a challenge for me.
A cast from integer is