diff options
| author | Tom Lane <tgl@sss.pgh.pa.us> | 2021-01-01 15:51:09 -0500 |
|---|---|---|
| committer | Tom Lane <tgl@sss.pgh.pa.us> | 2021-01-01 15:51:09 -0500 |
| commit | 4d3f03f42227bb351c2021a9ccea2fff9c023cfc (patch) | |
| tree | aed2198d85969b1bb29eaa48da7d546fd81652b0 | |
| parent | 32d6287d2eef6b6a4dde07e0513f3e4f321792ad (diff) | |
| download | postgresql-4d3f03f42227bb351c2021a9ccea2fff9c023cfc.tar.gz postgresql-4d3f03f42227bb351c2021a9ccea2fff9c023cfc.zip | |
Doc: improve explanation of EXTRACT(EPOCH) for timestamp without tz.
Try to be clearer about what computation is actually happening here.
Per bug #16797 from Dana Burd.
Discussion: https://postgr.es/m/16797-f264b0b980b53b8b@postgresql.org
| -rw-r--r-- | doc/src/sgml/func.sgml | 19 |
1 files changed, 16 insertions, 3 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1e044b51a16..02a37658ad9 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -9354,9 +9354,11 @@ SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); <listitem> <para> For <type>timestamp with time zone</type> values, the - number of seconds since 1970-01-01 00:00:00 UTC (can be negative); + number of seconds since 1970-01-01 00:00:00 UTC (negative for + timestamps before that); for <type>date</type> and <type>timestamp</type> values, the - number of seconds since 1970-01-01 00:00:00 local time; + nominal number of seconds since 1970-01-01 00:00:00, + without regard to timezone or daylight-savings rules; for <type>interval</type> values, the total number of seconds in the interval </para> @@ -9365,18 +9367,29 @@ SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40'); SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08'); <lineannotation>Result: </lineannotation><computeroutput>982384720.12</computeroutput> +SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12'); +<lineannotation>Result: </lineannotation><computeroutput>982355920.12</computeroutput> + SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); <lineannotation>Result: </lineannotation><computeroutput>442800</computeroutput> </screen> <para> - You can convert an epoch value back to a time stamp + You can convert an epoch value back to a <type>timestamp with time zone</type> with <function>to_timestamp</function>: </para> <screen> SELECT to_timestamp(982384720.12); <lineannotation>Result: </lineannotation><computeroutput>2001-02-17 04:38:40.12+00</computeroutput> </screen> + + <para> + Beware that applying <function>to_timestamp</function> to an epoch + extracted from a <type>date</type> or <type>timestamp</type> value + could produce a misleading result: the result will effectively + assume that the original value had been given in UTC, which might + not be the case. + </para> </listitem> </varlistentry> |