From najm@flipr.com Wed Jun 20 16:38:56 2001 Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5KGcua90499 for ; Wed, 20 Jun 2001 12:38:56 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from mail1.hub.org (webmail.hub.org [216.126.85.1]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5JFxTa48318 for ; Tue, 19 Jun 2001 11:59:29 -0400 (EDT) (envelope-from najm@flipr.com) Received: from najm.local2.lan ([216.221.221.2]) by mail1.hub.org (8.11.3/8.11.1) with ESMTP id f5JFkSG42422 for ; Tue, 19 Jun 2001 11:46:32 -0400 (EDT) (envelope-from najm@flipr.com) Received: from flipr.com (IDENT:najm@localhost.localdomain [127.0.0.1]) by najm.local2.lan (8.9.3/8.9.3) with ESMTP id LAA01941 for ; Tue, 19 Jun 2001 11:46:02 -0400 Message-ID: <3B2F73BA.8EBA2FD1@flipr.com> Date: Tue, 19 Jun 2001 11:46:02 -0400 From: Najm Hashmi X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.14-5.0 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql Subject: Extracting date from epoche Content-Type: multipart/alternative; boundary="------------F229CDDCB00EAC0E7683D854" X-Archive-Number: 200106/312 X-Sequence-Number: 3577 --------------F229CDDCB00EAC0E7683D854 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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, -- Najm Hashmi Tel:514-271-9791 www.mondo-live.com www.flipr.com --------------F229CDDCB00EAC0E7683D854 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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,
-- 
Najm Hashmi
Tel:514-271-9791
www.mondo-live.com
www.flipr.com
  --------------F229CDDCB00EAC0E7683D854-- From dev@archonet.com Thu Jun 21 09:33:20 2001 Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5L9XJa67946 for ; Thu, 21 Jun 2001 05:33:20 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 2.12 #1) id 15D0r5-0006se-0V; Thu, 21 Jun 2001 10:34:28 +0100 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 78FE613A71; Thu, 21 Jun 2001 10:11:20 +0100 (BST) Message-ID: <009c01c0fa32$21fb4cc0$1001a8c0@archonet.com> From: "Richard Huxton" To: "pgsql" , "Najm Hashmi" Cc: "Tom Lane" References: <3B2F73BA.8EBA2FD1@flipr.com> Subject: Re: Extracting date from epoche Date: Thu, 21 Jun 2001 10:11:17 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200106/325 X-Sequence-Number: 3612 ----- Original Message ----- From: "Najm Hashmi" To: "pgsql" 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 From tgl@sss.pgh.pa.us Thu Jun 21 14:05:15 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5LE5Fa86555 for ; Thu, 21 Jun 2001 10:05:15 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f5LE57313012; Thu, 21 Jun 2001 10:05:07 -0400 (EDT) To: "Richard Huxton" cc: "pgsql" , "Najm Hashmi" Subject: Re: Extracting date from epoche In-reply-to: <009c01c0fa32$21fb4cc0$1001a8c0@archonet.com> References: <3B2F73BA.8EBA2FD1@flipr.com> <009c01c0fa32$21fb4cc0$1001a8c0@archonet.com> Comments: In-reply-to "Richard Huxton" message dated "Thu, 21 Jun 2001 10:11:17 +0100" Date: Thu, 21 Jun 2001 10:05:07 -0400 Message-ID: <13009.993132307@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200106/328 X-Sequence-Number: 3615 "Richard Huxton" 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 dev@archonet.com Thu Jun 21 18:03:18 2001 Received: from finch-post-11.mail.demon.net (finch-post-11.mail.demon.net [194.217.242.39]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5LI3Ia96978 for ; Thu, 21 Jun 2001 14:03:18 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by finch-post-11.mail.demon.net with esmtp (Exim 2.12 #1) id 15D8nU-000JRv-0B; Thu, 21 Jun 2001 18:03:16 +0000 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id AB37813650; Thu, 21 Jun 2001 18:47:11 +0100 (BST) Message-ID: <004901c0fa7a$329d5620$1001a8c0@archonet.com> From: "Richard Huxton" To: "Tom Lane" Cc: "pgsql" , "Najm Hashmi" References: <3B2F73BA.8EBA2FD1@flipr.com> <009c01c0fa32$21fb4cc0$1001a8c0@archonet.com> <13009.993132307@sss.pgh.pa.us> Subject: Re: Extracting date from epoche Date: Thu, 21 Jun 2001 18:47:09 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200106/337 X-Sequence-Number: 3624 From: "Tom Lane" > "Richard Huxton" 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