X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id ACF9AD1DCEB for ; Fri, 22 Aug 2003 09:57:20 -0300 (ADT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 24056-04 for ; Fri, 22 Aug 2003 09:56:53 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 85490D1B4E2 for ; Fri, 22 Aug 2003 09:56:49 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h7MCuo5H006306; Fri, 22 Aug 2003 08:56:50 -0400 (EDT) To: Tomasz Myrta Cc: MichaelHoeller@t-online.de, pgsql-sql@postgresql.org Subject: Re: date calculation In-reply-to: <3F45BB45.80704@klaster.net> References: <1061533501.3f45b73d6566d@webmail.t-online.de> <3F45BB45.80704@klaster.net> Comments: In-reply-to Tomasz Myrta message dated "Fri, 22 Aug 2003 08:42:13 +0200" Date: Fri, 22 Aug 2003 08:56:50 -0400 Message-ID: <6305.1061557010@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/273 X-Sequence-Number: 14889 Tomasz Myrta writes: > cast('1970-1-1' as timestamp)+cast(your_ticks || ' seconds' as interval) > Does anyone know better way to cast it? The above will probably give the wrong answer (off by your timezone offset). The reference point should be zero hour GMT, but the first cast will give zero hour local time. You should cast the reference to timestamp with time zone (timestamptz) instead. Also, I'd suggest using float-times-interval as a simpler and more efficient way of forming the offset interval. So: select 'epoch'::timestamp with time zone + your_ticks * '1 second'::interval; regards, tom lane