Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bAIho-0006mC-3x for pgsql-docs@arkaria.postgresql.org; Tue, 07 Jun 2016 15:14:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bAIhn-0008Mj-NI for pgsql-docs@arkaria.postgresql.org; Tue, 07 Jun 2016 15:14:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bAIhn-0008Mb-BM for pgsql-docs@postgresql.org; Tue, 07 Jun 2016 15:14:11 +0000 Received: from mail-oi0-x232.google.com ([2607:f8b0:4003:c06::232]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bAIhi-0007Ji-RB for pgsql-docs@postgresql.org; Tue, 07 Jun 2016 15:14:10 +0000 Received: by mail-oi0-x232.google.com with SMTP id k23so281597400oih.0 for ; Tue, 07 Jun 2016 08:14:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pinpointresearch-com.20150623.gappssmtp.com; s=20150623; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc; bh=uybm0uLkGRnLy0kmf+VXvvJmL6k38unOnYU5zjtVc6o=; b=xR1qVtpmljD/HbIM7DjbYsCfRWxeK3LesHCkQc7ijqr/LfFf7j58Ux0VVmz7+BRaP/ 73wsBKJ2DscVQE9ZT6XjbnUkK5zfSvbLpjcvt65dBLhX7p3D09uMTDonZGKBgtUnGHL/ 3CNwcN7rqexT71YEm6ao4E7KbqfEchJOhXa8tByMj5RN4WIZx8rfwDWIHOULMvhbSdNw wZC7HN6WRVzEGFQTP4CsVfnxGITScWbsJab6ASBCTbw2dRUOtVtpAYYMvdoHcE99+hA2 YU/YDMHW1l8k9yN+juiVAtHeAViHTu9QBS7TSg3Mz8nQMQ1MKuOf8rtSrIjaElONT8gM 2hjw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc; bh=uybm0uLkGRnLy0kmf+VXvvJmL6k38unOnYU5zjtVc6o=; b=P54QE9ExXjsbGiy6PrfvedifVAbQBPVmJvCBPRu4gRYVOLttrv5N84Pjeo4dV3quz7 6q4IMXJQGcMbcGZW0jzweJ5x111wFYn8qnw5fQ0783KvsODwfiQO3TFC1pmPq0oYiy3O Fry2uL9AJ0lHpGrDfzUFJ6NERfFwGx37DGi8LFiVsuYUz1sYahKs3LVm3OxBzbyEIA7M qsbW/uJxcR5Fwci/S5jPljBcSRmMWwyaeIsm8AUfHaEiYaYcS42YJcGkmWDpy5SWJUYN IObrunOZNI9MwTh/cE1Ys8izzLt3cjIO9xpMyfEwko52UIOrgeYY29yTEPHsY9+c2Eml p3kQ== X-Gm-Message-State: ALyK8tInc+V36qk9GpFhiOpq+HjphGbkWu7NOTdSeV/jHJjEKbcTdvR6V93uI4+kRg4r9LRcvmrOpTtFxsV8l2U3 MIME-Version: 1.0 X-Received: by 10.202.226.78 with SMTP id z75mr9443633oig.74.1465312444405; Tue, 07 Jun 2016 08:14:04 -0700 (PDT) Received: by 10.182.39.165 with HTTP; Tue, 7 Jun 2016 08:14:04 -0700 (PDT) In-Reply-To: <20160603234404.9927.3198@wrigleys.postgresql.org> References: <20160603234404.9927.3198@wrigleys.postgresql.org> Date: Tue, 7 Jun 2016 08:14:04 -0700 Message-ID: Subject: Re: Indicated Epoch 0 is incorrect From: Steve Crawford To: ian@thepathcentral.com Cc: pgsql-docs@postgresql.org Content-Type: multipart/alternative; boundary=001a1141b37cbbf6f90534b1a3b8 X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org --001a1141b37cbbf6f90534b1a3b8 Content-Type: text/plain; charset=UTF-8 The docs are correct. When you convert a string to a timestamp with time zone it will interpret the string in *your* time zone which is apparently offset from UTC. Try: select extract(epoch from timestamp with time zone '1970-01-01 00:00:00-00'); or select extract(epoch from timestamp with time zone '1970-01-01 00:00:00 UTC'); Note the explicit definition of timezone offset of zero or time zone of UTC. Cheers, Steve On Fri, Jun 3, 2016 at 4:44 PM, wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/9.1/static/functions-datetime.html > Description: > > Per /docs/current/static/functions-datetime.html (and earlier), epoch is > defined as: > > "For date and timestamp values, the number of seconds since 1970-01-01 > 00:00:00 UTC". > > The correct value to obtain 0 is as follows: > > SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '1970-01-01 > 8:00:00.0') > > -- > Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-docs > --001a1141b37cbbf6f90534b1a3b8 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
The docs are correct. When you convert a string to a times= tamp with time zone it will interpret the string in *your* time zone which = is apparently offset from UTC.

Try:
select extract(epoch from timestamp with = time zone '1970-01-01 00:00:00-00');
or
select extract(epoch f= rom timestamp with time zone '1970-01-01 00:00:00 UTC');

Note the explicit definiti= on of timezone offset of zero or time zone of UTC.

Cheers,
Steve

On Fri, Jun 3, 2016 at 4:44 PM, <<= a href=3D"mailto:ian@thepathcentral.com" target=3D"_blank">ian@thepathcentr= al.com> wrote:
The followin= g documentation comment has been logged on the website:

Page: https://www.postgresql.org/d= ocs/9.1/static/functions-datetime.html
Description:

Per /docs/current/static/functions-datetime.html (and earlier), epoch is defined as:

&quot;For date and timestamp values, the number of seconds since 1970-0= 1-01
00:00:00 UTC&quot;.

The correct value to obtain 0 is as follows:

SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE &#39;1970-01-01 8:00= :00.0&#39;)

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

--001a1141b37cbbf6f90534b1a3b8--