Re: [HACKERS] Converting epoch to timestamp
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 to use EPOCH as a datatype though. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Converting epoch to timestamp
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)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Converting epoch to timestamp
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 probably a bad idea, seeing that it will break in 2038. You could make an argument for a cast from double though. The issue to my mind is whether this might be too Unix-centric. In my mind, epoch is pretty Unix-centric. In IRC we often see people who want to store timestamps in their db as strings or integers, which we are sure to point out isn't necessarily the best way to take advantage of PostgreSQL's strengths in handling timestamps. Having these (corrected) functions available would be enough in my mind. The casting idea came about when I was thinking about where I'd put the functions in the documentation. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Converting epoch to timestamp
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Converting epoch to timestamp
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 Eastern zone which is presently GMT-4). I get I would actually prefer this syntax: SELECT EXTRACT(EPOCH FROM TIMESTAMP '2003-01-01 11:23:44'); Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Converting epoch to timestamp
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: 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 Eastern zone which is presently GMT-4). I get regression=# select 'epoch'::timestamp + 1089953023 * '1 second'::interval; ?column? - 2004-07-16 04:43:43 (1 row) regression=# select 'epoch'::timestamptz + 1089953023 * '1 second'::interval; ?column? 2004-07-16 00:43:43-04 (1 row) The first is not right, the second is ... > I'm wondering if this wouldn't be better as cast rather than explicit > functions. A cast from integer is probably a bad idea, seeing that it will break in 2038. You could make an argument for a cast from double though. The issue to my mind is whether this might be too Unix-centric. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Converting epoch to timestamp
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 epoch-to-timestamp converstion more convenient. I don't know if they would be more efficient if written in C. create or replace function epoch_to_timestamp( integer ) returns timestamp(0) language sql as ' SELECT ''epoch''::timestamp + $1 * ''1 second''::interval; '; create or replace function epoch_to_timestamptz( double precision ) returns timestamptz language sql as ' SELECT (''epoch''::timestamp + $1 * ''1 second''::interval) at time zone ''UTC''; '; The first function, epoch_to_timestamp, conforms to the normal definition of epoch, which is integer seconds. It returns timestamp at UTC. The second function, epoch_to_timestamptz, provides an inverse to extract(epoch from timestamp), which returns double precision. I'm wondering if this wouldn't be better as cast rather than explicit functions. I'm interested in hearing people's thoughts, whether or not these would be useful additions to PostgreSQL and whether this particular implementation is appropriate. If it is, I'll write up some docs for the appropriate section, with any modifications people might suggest. Thank you for your time. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match