Thread: Extracting date from epoche
Hi I have some data that is supose to be a date but in ecpoche format. How can I reonvert it to data format. <br />Thanksin advance for help. <br />Regards, <pre>-- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com</pre>
----- Original Message ----- From: "Najm Hashmi" <najm@flipr.com> To: "pgsql" <pgsql-sql@postgresql.org> Sent: Tuesday, June 19, 2001 4:46 PM Subject: [SQL] Extracting date from epoche > Hi I have some data that is supose to be a date but in ecpoche > format. How can I reonvert it to data format. > Thanks in advance for help. > Regards, Something like: select '1970-01-01 00:00:00+00'::timestamp + (3600::text || ' seconds')::interval; should work (3600 is your offset, the 1970 assumes std unix epoch start). Keep an eye on timezone issues. Example: richardh=> select 'epoch'::date + (3600::text || ' seconds')::interval; ?column? ------------------------1970-01-01 01:00:00+01 (1 row) richardh=> select '1970-01-01 00:00:00+00'::timestamp + (3600::text || ' seconds')::interval; ?column? ------------------------1970-01-01 02:00:00+01 (1 row) The difference is because I am currently in timezone +01 and 'epoch' seems to assume my timezone. In the second example I explicitly set the timezone. Tom - I thought 'epoch'::timestamp should work too - good reason, or just One Of Those Things (tm)? - Richard Huxton
"Richard Huxton" <dev@archonet.com> writes: > Tom - I thought 'epoch'::timestamp should work too - good reason, or just > One Of Those Things (tm)? It works --- it's a symbolic value, though. regression=# select 'epoch'::timestamp;?column? ----------epoch (1 row) regression=# select 'epoch'::timestamp + ('1 day'::interval); ?column? ------------------------1970-01-01 19:00:00-05 (1 row) Note the epoch is midnight GMT = 7pm local time here. regards, tom lane
From: "Tom Lane" <tgl@sss.pgh.pa.us> > "Richard Huxton" <dev@archonet.com> writes: > > Tom - I thought 'epoch'::timestamp should work too - good reason, or just > > One Of Those Things (tm)? > > It works --- it's a symbolic value, though. > > regression=# select 'epoch'::timestamp; > ?column? > ---------- > epoch > (1 row) > > regression=# select 'epoch'::timestamp + ('1 day'::interval); > ?column? > ------------------------ > 1970-01-01 19:00:00-05 > (1 row) Ah - problem is in my wetware then. That's ok. - Richard Huxton