v1-0003-split-functions-datetime-sect1-from-func.sgml.patch
text/x-patch
Filename: v1-0003-split-functions-datetime-sect1-from-func.sgml.patch
Type: text/x-patch
Part: 5
Message:
Re: documentation structure
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v1-0003
Subject: split "functions-datetime" sect1 from func.sgml
| File | + | − |
|---|---|---|
| doc/src/sgml/filelist.sgml | 1 | 0 |
| doc/src/sgml/func-datetime.sgml | 2184 | 0 |
| doc/src/sgml/func.sgml | 1 | 2186 |
From a0239d2b4a117319cbbe9b3551ac6db632300837 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Fri, 26 Apr 2024 14:47:57 +0800
Subject: [PATCH v1 3/7] split "functions-datetime" sect1 from func.sgml
"functions-datetime" live in new file: doc/src/sgml/func-datetime.sgml,
it will hold doc/src/sgml/html/functions-datetime.html content.
http://postgr.es/m/CACJufxEcMjjn-m6fpC2wXHsQbE5nyd%3Dxt6k-jDizBVUKK6O4KQ%40mail.gmail.com
---
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/func-datetime.sgml | 2184 ++++++++++++++++++++++++++++++
doc/src/sgml/func.sgml | 2187 +------------------------------
3 files changed, 2186 insertions(+), 2186 deletions(-)
create mode 100644 doc/src/sgml/func-datetime.sgml
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 13bc4be5..1b6dc08e 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -20,6 +20,7 @@
<!ENTITY func SYSTEM "func.sgml">
<!ENTITY func-string SYSTEM "func-string.sgml">
<!ENTITY func-matching SYSTEM "func-matching.sgml">
+<!ENTITY func-datetime SYSTEM "func-datetime.sgml">
<!ENTITY indices SYSTEM "indices.sgml">
<!ENTITY json SYSTEM "json.sgml">
<!ENTITY mvcc SYSTEM "mvcc.sgml">
diff --git a/doc/src/sgml/func-datetime.sgml b/doc/src/sgml/func-datetime.sgml
new file mode 100644
index 00000000..0410d854
--- /dev/null
+++ b/doc/src/sgml/func-datetime.sgml
@@ -0,0 +1,2184 @@
+ <sect1 id="functions-datetime">
+ <title>Date/Time Functions and Operators</title>
+
+ <para>
+ <xref linkend="functions-datetime-table"/> shows the available
+ functions for date/time value processing, with details appearing in
+ the following subsections. <xref
+ linkend="operators-datetime-table"/> illustrates the behaviors of
+ the basic arithmetic operators (<literal>+</literal>,
+ <literal>*</literal>, etc.). For formatting functions, refer to
+ <xref linkend="functions-formatting"/>. You should be familiar with
+ the background information on date/time data types from <xref
+ linkend="datatype-datetime"/>.
+ </para>
+
+ <para>
+ In addition, the usual comparison operators shown in
+ <xref linkend="functions-comparison-op-table"/> are available for the
+ date/time types. Dates and timestamps (with or without time zone) are
+ all comparable, while times (with or without time zone) and intervals
+ can only be compared to other values of the same data type. When
+ comparing a timestamp without time zone to a timestamp with time zone,
+ the former value is assumed to be given in the time zone specified by
+ the <xref linkend="guc-timezone"/> configuration parameter, and is
+ rotated to UTC for comparison to the latter value (which is already
+ in UTC internally). Similarly, a date value is assumed to represent
+ midnight in the <varname>TimeZone</varname> zone when comparing it
+ to a timestamp.
+ </para>
+
+ <para>
+ All the functions and operators described below that take <type>time</type> or <type>timestamp</type>
+ inputs actually come in two variants: one that takes <type>time with time zone</type> or <type>timestamp
+ with time zone</type>, and one that takes <type>time without time zone</type> or <type>timestamp without time zone</type>.
+ For brevity, these variants are not shown separately. Also, the
+ <literal>+</literal> and <literal>*</literal> operators come in commutative pairs (for
+ example both <type>date</type> <literal>+</literal> <type>integer</type>
+ and <type>integer</type> <literal>+</literal> <type>date</type>); we show
+ only one of each such pair.
+ </para>
+
+ <table id="operators-datetime-table">
+ <title>Date/Time Operators</title>
+
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Operator
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>date</type> <literal>+</literal> <type>integer</type>
+ <returnvalue>date</returnvalue>
+ </para>
+ <para>
+ Add a number of days to a date
+ </para>
+ <para>
+ <literal>date '2001-09-28' + 7</literal>
+ <returnvalue>2001-10-05</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>date</type> <literal>+</literal> <type>interval</type>
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Add an interval to a date
+ </para>
+ <para>
+ <literal>date '2001-09-28' + interval '1 hour'</literal>
+ <returnvalue>2001-09-28 01:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>date</type> <literal>+</literal> <type>time</type>
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Add a time-of-day to a date
+ </para>
+ <para>
+ <literal>date '2001-09-28' + time '03:00'</literal>
+ <returnvalue>2001-09-28 03:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>interval</type> <literal>+</literal> <type>interval</type>
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Add intervals
+ </para>
+ <para>
+ <literal>interval '1 day' + interval '1 hour'</literal>
+ <returnvalue>1 day 01:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>timestamp</type> <literal>+</literal> <type>interval</type>
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Add an interval to a timestamp
+ </para>
+ <para>
+ <literal>timestamp '2001-09-28 01:00' + interval '23 hours'</literal>
+ <returnvalue>2001-09-29 00:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>time</type> <literal>+</literal> <type>interval</type>
+ <returnvalue>time</returnvalue>
+ </para>
+ <para>
+ Add an interval to a time
+ </para>
+ <para>
+ <literal>time '01:00' + interval '3 hours'</literal>
+ <returnvalue>04:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <literal>-</literal> <type>interval</type>
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Negate an interval
+ </para>
+ <para>
+ <literal>- interval '23 hours'</literal>
+ <returnvalue>-23:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>date</type> <literal>-</literal> <type>date</type>
+ <returnvalue>integer</returnvalue>
+ </para>
+ <para>
+ Subtract dates, producing the number of days elapsed
+ </para>
+ <para>
+ <literal>date '2001-10-01' - date '2001-09-28'</literal>
+ <returnvalue>3</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>date</type> <literal>-</literal> <type>integer</type>
+ <returnvalue>date</returnvalue>
+ </para>
+ <para>
+ Subtract a number of days from a date
+ </para>
+ <para>
+ <literal>date '2001-10-01' - 7</literal>
+ <returnvalue>2001-09-24</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>date</type> <literal>-</literal> <type>interval</type>
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Subtract an interval from a date
+ </para>
+ <para>
+ <literal>date '2001-09-28' - interval '1 hour'</literal>
+ <returnvalue>2001-09-27 23:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>time</type> <literal>-</literal> <type>time</type>
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Subtract times
+ </para>
+ <para>
+ <literal>time '05:00' - time '03:00'</literal>
+ <returnvalue>02:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>time</type> <literal>-</literal> <type>interval</type>
+ <returnvalue>time</returnvalue>
+ </para>
+ <para>
+ Subtract an interval from a time
+ </para>
+ <para>
+ <literal>time '05:00' - interval '2 hours'</literal>
+ <returnvalue>03:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>timestamp</type> <literal>-</literal> <type>interval</type>
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Subtract an interval from a timestamp
+ </para>
+ <para>
+ <literal>timestamp '2001-09-28 23:00' - interval '23 hours'</literal>
+ <returnvalue>2001-09-28 00:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>interval</type> <literal>-</literal> <type>interval</type>
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Subtract intervals
+ </para>
+ <para>
+ <literal>interval '1 day' - interval '1 hour'</literal>
+ <returnvalue>1 day -01:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>timestamp</type> <literal>-</literal> <type>timestamp</type>
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Subtract timestamps (converting 24-hour intervals into days,
+ similarly to <link
+ linkend="function-justify-hours"><function>justify_hours()</function></link>)
+ </para>
+ <para>
+ <literal>timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'</literal>
+ <returnvalue>63 days 15:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>interval</type> <literal>*</literal> <type>double precision</type>
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Multiply an interval by a scalar
+ </para>
+ <para>
+ <literal>interval '1 second' * 900</literal>
+ <returnvalue>00:15:00</returnvalue>
+ </para>
+ <para>
+ <literal>interval '1 day' * 21</literal>
+ <returnvalue>21 days</returnvalue>
+ </para>
+ <para>
+ <literal>interval '1 hour' * 3.5</literal>
+ <returnvalue>03:30:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>interval</type> <literal>/</literal> <type>double precision</type>
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Divide an interval by a scalar
+ </para>
+ <para>
+ <literal>interval '1 hour' / 1.5</literal>
+ <returnvalue>00:40:00</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="functions-datetime-table">
+ <title>Date/Time Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>age</primary>
+ </indexterm>
+ <function>age</function> ( <type>timestamp</type>, <type>timestamp</type> )
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Subtract arguments, producing a <quote>symbolic</quote> result that
+ uses years and months, rather than just days
+ </para>
+ <para>
+ <literal>age(timestamp '2001-04-10', timestamp '1957-06-13')</literal>
+ <returnvalue>43 years 9 mons 27 days</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>age</function> ( <type>timestamp</type> )
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Subtract argument from <function>current_date</function> (at midnight)
+ </para>
+ <para>
+ <literal>age(timestamp '1957-06-13')</literal>
+ <returnvalue>62 years 6 mons 10 days</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>clock_timestamp</primary>
+ </indexterm>
+ <function>clock_timestamp</function> ( )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Current date and time (changes during statement execution);
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>clock_timestamp()</literal>
+ <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>current_date</primary>
+ </indexterm>
+ <function>current_date</function>
+ <returnvalue>date</returnvalue>
+ </para>
+ <para>
+ Current date; see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>current_date</literal>
+ <returnvalue>2019-12-23</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>current_time</primary>
+ </indexterm>
+ <function>current_time</function>
+ <returnvalue>time with time zone</returnvalue>
+ </para>
+ <para>
+ Current time of day; see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>current_time</literal>
+ <returnvalue>14:39:53.662522-05</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>current_time</function> ( <type>integer</type> )
+ <returnvalue>time with time zone</returnvalue>
+ </para>
+ <para>
+ Current time of day, with limited precision;
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>current_time(2)</literal>
+ <returnvalue>14:39:53.66-05</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>current_timestamp</primary>
+ </indexterm>
+ <function>current_timestamp</function>
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Current date and time (start of current transaction);
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>current_timestamp</literal>
+ <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>current_timestamp</function> ( <type>integer</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Current date and time (start of current transaction), with limited precision;
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>current_timestamp(0)</literal>
+ <returnvalue>2019-12-23 14:39:53-05</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>date_add</primary>
+ </indexterm>
+ <function>date_add</function> ( <type>timestamp with time zone</type>, <type>interval</type> <optional>, <type>text</type> </optional> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Add an <type>interval</type> to a <type>timestamp with time
+ zone</type>, computing times of day and daylight-savings adjustments
+ according to the time zone named by the third argument, or the
+ current <xref linkend="guc-timezone"/> setting if that is omitted.
+ The form with two arguments is equivalent to the <type>timestamp with
+ time zone</type> <literal>+</literal> <type>interval</type> operator.
+ </para>
+ <para>
+ <literal>date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</literal>
+ <returnvalue>2021-10-31 23:00:00+00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>date_bin</function> ( <type>interval</type>, <type>timestamp</type>, <type>timestamp</type> )
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Bin input into specified interval aligned with specified origin; see <xref linkend="functions-datetime-bin"/>
+ </para>
+ <para>
+ <literal>date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</literal>
+ <returnvalue>2001-02-16 20:35:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>date_part</primary>
+ </indexterm>
+ <function>date_part</function> ( <type>text</type>, <type>timestamp</type> )
+ <returnvalue>double precision</returnvalue>
+ </para>
+ <para>
+ Get timestamp subfield (equivalent to <function>extract</function>);
+ see <xref linkend="functions-datetime-extract"/>
+ </para>
+ <para>
+ <literal>date_part('hour', timestamp '2001-02-16 20:38:40')</literal>
+ <returnvalue>20</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>date_part</function> ( <type>text</type>, <type>interval</type> )
+ <returnvalue>double precision</returnvalue>
+ </para>
+ <para>
+ Get interval subfield (equivalent to <function>extract</function>);
+ see <xref linkend="functions-datetime-extract"/>
+ </para>
+ <para>
+ <literal>date_part('month', interval '2 years 3 months')</literal>
+ <returnvalue>3</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>date_subtract</primary>
+ </indexterm>
+ <function>date_subtract</function> ( <type>timestamp with time zone</type>, <type>interval</type> <optional>, <type>text</type> </optional> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Subtract an <type>interval</type> from a <type>timestamp with time
+ zone</type>, computing times of day and daylight-savings adjustments
+ according to the time zone named by the third argument, or the
+ current <xref linkend="guc-timezone"/> setting if that is omitted.
+ The form with two arguments is equivalent to the <type>timestamp with
+ time zone</type> <literal>-</literal> <type>interval</type> operator.
+ </para>
+ <para>
+ <literal>date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</literal>
+ <returnvalue>2021-10-30 22:00:00+00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>date_trunc</primary>
+ </indexterm>
+ <function>date_trunc</function> ( <type>text</type>, <type>timestamp</type> )
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Truncate to specified precision; see <xref linkend="functions-datetime-trunc"/>
+ </para>
+ <para>
+ <literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal>
+ <returnvalue>2001-02-16 20:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>date_trunc</function> ( <type>text</type>, <type>timestamp with time zone</type>, <type>text</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Truncate to specified precision in the specified time zone; see
+ <xref linkend="functions-datetime-trunc"/>
+ </para>
+ <para>
+ <literal>date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')</literal>
+ <returnvalue>2001-02-16 13:00:00+00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>date_trunc</function> ( <type>text</type>, <type>interval</type> )
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Truncate to specified precision; see
+ <xref linkend="functions-datetime-trunc"/>
+ </para>
+ <para>
+ <literal>date_trunc('hour', interval '2 days 3 hours 40 minutes')</literal>
+ <returnvalue>2 days 03:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>extract</primary>
+ </indexterm>
+ <function>extract</function> ( <parameter>field</parameter> <literal>from</literal> <type>timestamp</type> )
+ <returnvalue>numeric</returnvalue>
+ </para>
+ <para>
+ Get timestamp subfield; see <xref linkend="functions-datetime-extract"/>
+ </para>
+ <para>
+ <literal>extract(hour from timestamp '2001-02-16 20:38:40')</literal>
+ <returnvalue>20</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>extract</function> ( <parameter>field</parameter> <literal>from</literal> <type>interval</type> )
+ <returnvalue>numeric</returnvalue>
+ </para>
+ <para>
+ Get interval subfield; see <xref linkend="functions-datetime-extract"/>
+ </para>
+ <para>
+ <literal>extract(month from interval '2 years 3 months')</literal>
+ <returnvalue>3</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>isfinite</primary>
+ </indexterm>
+ <function>isfinite</function> ( <type>date</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Test for finite date (not +/-infinity)
+ </para>
+ <para>
+ <literal>isfinite(date '2001-02-16')</literal>
+ <returnvalue>true</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>isfinite</function> ( <type>timestamp</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Test for finite timestamp (not +/-infinity)
+ </para>
+ <para>
+ <literal>isfinite(timestamp 'infinity')</literal>
+ <returnvalue>false</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>isfinite</function> ( <type>interval</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Test for finite interval (not +/-infinity)
+ </para>
+ <para>
+ <literal>isfinite(interval '4 hours')</literal>
+ <returnvalue>true</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm id="function-justify-days">
+ <primary>justify_days</primary>
+ </indexterm>
+ <function>justify_days</function> ( <type>interval</type> )
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Adjust interval, converting 30-day time periods to months
+ </para>
+ <para>
+ <literal>justify_days(interval '1 year 65 days')</literal>
+ <returnvalue>1 year 2 mons 5 days</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm id="function-justify-hours">
+ <primary>justify_hours</primary>
+ </indexterm>
+ <function>justify_hours</function> ( <type>interval</type> )
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Adjust interval, converting 24-hour time periods to days
+ </para>
+ <para>
+ <literal>justify_hours(interval '50 hours 10 minutes')</literal>
+ <returnvalue>2 days 02:10:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>justify_interval</primary>
+ </indexterm>
+ <function>justify_interval</function> ( <type>interval</type> )
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Adjust interval using <function>justify_days</function>
+ and <function>justify_hours</function>, with additional sign
+ adjustments
+ </para>
+ <para>
+ <literal>justify_interval(interval '1 mon -1 hour')</literal>
+ <returnvalue>29 days 23:00:00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>localtime</primary>
+ </indexterm>
+ <function>localtime</function>
+ <returnvalue>time</returnvalue>
+ </para>
+ <para>
+ Current time of day;
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>localtime</literal>
+ <returnvalue>14:39:53.662522</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>localtime</function> ( <type>integer</type> )
+ <returnvalue>time</returnvalue>
+ </para>
+ <para>
+ Current time of day, with limited precision;
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>localtime(0)</literal>
+ <returnvalue>14:39:53</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>localtimestamp</primary>
+ </indexterm>
+ <function>localtimestamp</function>
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Current date and time (start of current transaction);
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>localtimestamp</literal>
+ <returnvalue>2019-12-23 14:39:53.662522</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>localtimestamp</function> ( <type>integer</type> )
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Current date and time (start of current
+ transaction), with limited precision;
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>localtimestamp(2)</literal>
+ <returnvalue>2019-12-23 14:39:53.66</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>make_date</primary>
+ </indexterm>
+ <function>make_date</function> ( <parameter>year</parameter> <type>int</type>,
+ <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type> )
+ <returnvalue>date</returnvalue>
+ </para>
+ <para>
+ Create date from year, month and day fields
+ (negative years signify BC)
+ </para>
+ <para>
+ <literal>make_date(2013, 7, 15)</literal>
+ <returnvalue>2013-07-15</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature"><indexterm>
+ <primary>make_interval</primary>
+ </indexterm>
+ <function>make_interval</function> ( <optional> <parameter>years</parameter> <type>int</type>
+ <optional>, <parameter>months</parameter> <type>int</type>
+ <optional>, <parameter>weeks</parameter> <type>int</type>
+ <optional>, <parameter>days</parameter> <type>int</type>
+ <optional>, <parameter>hours</parameter> <type>int</type>
+ <optional>, <parameter>mins</parameter> <type>int</type>
+ <optional>, <parameter>secs</parameter> <type>double precision</type>
+ </optional></optional></optional></optional></optional></optional></optional> )
+ <returnvalue>interval</returnvalue>
+ </para>
+ <para>
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields, each of which can default to zero
+ </para>
+ <para>
+ <literal>make_interval(days => 10)</literal>
+ <returnvalue>10 days</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>make_time</primary>
+ </indexterm>
+ <function>make_time</function> ( <parameter>hour</parameter> <type>int</type>,
+ <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type> )
+ <returnvalue>time</returnvalue>
+ </para>
+ <para>
+ Create time from hour, minute and seconds fields
+ </para>
+ <para>
+ <literal>make_time(8, 15, 23.5)</literal>
+ <returnvalue>08:15:23.5</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>make_timestamp</primary>
+ </indexterm>
+ <function>make_timestamp</function> ( <parameter>year</parameter> <type>int</type>,
+ <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>,
+ <parameter>hour</parameter> <type>int</type>,
+ <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type> )
+ <returnvalue>timestamp</returnvalue>
+ </para>
+ <para>
+ Create timestamp from year, month, day, hour, minute and seconds fields
+ (negative years signify BC)
+ </para>
+ <para>
+ <literal>make_timestamp(2013, 7, 15, 8, 15, 23.5)</literal>
+ <returnvalue>2013-07-15 08:15:23.5</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>make_timestamptz</primary>
+ </indexterm>
+ <function>make_timestamptz</function> ( <parameter>year</parameter> <type>int</type>,
+ <parameter>month</parameter> <type>int</type>,
+ <parameter>day</parameter> <type>int</type>,
+ <parameter>hour</parameter> <type>int</type>,
+ <parameter>min</parameter> <type>int</type>,
+ <parameter>sec</parameter> <type>double precision</type>
+ <optional>, <parameter>timezone</parameter> <type>text</type> </optional> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Create timestamp with time zone from year, month, day, hour, minute
+ and seconds fields (negative years signify BC).
+ If <parameter>timezone</parameter> is not
+ specified, the current time zone is used; the examples assume the
+ session time zone is <literal>Europe/London</literal>
+ </para>
+ <para>
+ <literal>make_timestamptz(2013, 7, 15, 8, 15, 23.5)</literal>
+ <returnvalue>2013-07-15 08:15:23.5+01</returnvalue>
+ </para>
+ <para>
+ <literal>make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York')</literal>
+ <returnvalue>2013-07-15 13:15:23.5+01</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>now</primary>
+ </indexterm>
+ <function>now</function> ( )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Current date and time (start of current transaction);
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>now()</literal>
+ <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>statement_timestamp</primary>
+ </indexterm>
+ <function>statement_timestamp</function> ( )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Current date and time (start of current statement);
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>statement_timestamp()</literal>
+ <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>timeofday</primary>
+ </indexterm>
+ <function>timeofday</function> ( )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Current date and time
+ (like <function>clock_timestamp</function>, but as a <type>text</type> string);
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>timeofday()</literal>
+ <returnvalue>Mon Dec 23 14:39:53.662522 2019 EST</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>transaction_timestamp</primary>
+ </indexterm>
+ <function>transaction_timestamp</function> ( )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Current date and time (start of current transaction);
+ see <xref linkend="functions-datetime-current"/>
+ </para>
+ <para>
+ <literal>transaction_timestamp()</literal>
+ <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>to_timestamp</primary>
+ </indexterm>
+ <function>to_timestamp</function> ( <type>double precision</type> )
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to
+ timestamp with time zone
+ </para>
+ <para>
+ <literal>to_timestamp(1284352323)</literal>
+ <returnvalue>2010-09-13 04:32:03+00</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ <indexterm>
+ <primary>OVERLAPS</primary>
+ </indexterm>
+ In addition to these functions, the SQL <literal>OVERLAPS</literal> operator is
+ supported:
+<synopsis>
+(<replaceable>start1</replaceable>, <replaceable>end1</replaceable>) OVERLAPS (<replaceable>start2</replaceable>, <replaceable>end2</replaceable>)
+(<replaceable>start1</replaceable>, <replaceable>length1</replaceable>) OVERLAPS (<replaceable>start2</replaceable>, <replaceable>length2</replaceable>)
+</synopsis>
+ This expression yields true when two time periods (defined by their
+ endpoints) overlap, false when they do not overlap. The endpoints
+ can be specified as pairs of dates, times, or time stamps; or as
+ a date, time, or time stamp followed by an interval. When a pair
+ of values is provided, either the start or the end can be written
+ first; <literal>OVERLAPS</literal> automatically takes the earlier value
+ of the pair as the start. Each time period is considered to
+ represent the half-open interval <replaceable>start</replaceable> <literal><=</literal>
+ <replaceable>time</replaceable> <literal><</literal> <replaceable>end</replaceable>, unless
+ <replaceable>start</replaceable> and <replaceable>end</replaceable> are equal in which case it
+ represents that single time instant. This means for instance that two
+ time periods with only an endpoint in common do not overlap.
+ </para>
+
+<screen>
+SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
+ (DATE '2001-10-30', DATE '2002-10-30');
+<lineannotation>Result: </lineannotation><computeroutput>true</computeroutput>
+SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
+ (DATE '2001-10-30', DATE '2002-10-30');
+<lineannotation>Result: </lineannotation><computeroutput>false</computeroutput>
+SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
+ (DATE '2001-10-30', DATE '2001-10-31');
+<lineannotation>Result: </lineannotation><computeroutput>false</computeroutput>
+SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
+ (DATE '2001-10-30', DATE '2001-10-31');
+<lineannotation>Result: </lineannotation><computeroutput>true</computeroutput>
+</screen>
+
+ <para>
+ When adding an <type>interval</type> value to (or subtracting an
+ <type>interval</type> value from) a <type>timestamp</type>
+ or <type>timestamp with time zone</type> value, the months, days, and
+ microseconds fields of the <type>interval</type> value are handled in turn.
+ First, a nonzero months field advances or decrements the date of the
+ timestamp by the indicated number of months, keeping the day of month the
+ same unless it would be past the end of the new month, in which case the
+ last day of that month is used. (For example, March 31 plus 1 month
+ becomes April 30, but March 31 plus 2 months becomes May 31.)
+ Then the days field advances or decrements the date of the timestamp by
+ the indicated number of days. In both these steps the local time of day
+ is kept the same. Finally, if there is a nonzero microseconds field, it
+ is added or subtracted literally.
+ When doing arithmetic on a <type>timestamp with time zone</type> value in
+ a time zone that recognizes DST, this means that adding or subtracting
+ (say) <literal>interval '1 day'</literal> does not necessarily have the
+ same result as adding or subtracting <literal>interval '24
+ hours'</literal>.
+ For example, with the session time zone set
+ to <literal>America/Denver</literal>:
+<screen>
+SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';
+<lineannotation>Result: </lineannotation><computeroutput>2005-04-03 12:00:00-06</computeroutput>
+SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';
+<lineannotation>Result: </lineannotation><computeroutput>2005-04-03 13:00:00-06</computeroutput>
+</screen>
+ This happens because an hour was skipped due to a change in daylight saving
+ time at <literal>2005-04-03 02:00:00</literal> in time zone
+ <literal>America/Denver</literal>.
+ </para>
+
+ <para>
+ Note there can be ambiguity in the <literal>months</literal> field returned by
+ <function>age</function> because different months have different numbers of
+ days. <productname>PostgreSQL</productname>'s approach uses the month from the
+ earlier of the two dates when calculating partial months. For example,
+ <literal>age('2004-06-01', '2004-04-30')</literal> uses April to yield
+ <literal>1 mon 1 day</literal>, while using May would yield <literal>1 mon 2
+ days</literal> because May has 31 days, while April has only 30.
+ </para>
+
+ <para>
+ Subtraction of dates and timestamps can also be complex. One conceptually
+ simple way to perform subtraction is to convert each value to a number
+ of seconds using <literal>EXTRACT(EPOCH FROM ...)</literal>, then subtract the
+ results; this produces the
+ number of <emphasis>seconds</emphasis> between the two values. This will adjust
+ for the number of days in each month, timezone changes, and daylight
+ saving time adjustments. Subtraction of date or timestamp
+ values with the <quote><literal>-</literal></quote> operator
+ returns the number of days (24-hours) and hours/minutes/seconds
+ between the values, making the same adjustments. The <function>age</function>
+ function returns years, months, days, and hours/minutes/seconds,
+ performing field-by-field subtraction and then adjusting for negative
+ field values. The following queries illustrate the differences in these
+ approaches. The sample results were produced with <literal>timezone
+ = 'US/Eastern'</literal>; there is a daylight saving time change between the
+ two dates used:
+ </para>
+
+<screen>
+SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
+ EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
+<lineannotation>Result: </lineannotation><computeroutput>10537200.000000</computeroutput>
+SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
+ EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
+ / 60 / 60 / 24;
+<lineannotation>Result: </lineannotation><computeroutput>121.9583333333333333</computeroutput>
+SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
+<lineannotation>Result: </lineannotation><computeroutput>121 days 23:00:00</computeroutput>
+SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
+<lineannotation>Result: </lineannotation><computeroutput>4 mons</computeroutput>
+</screen>
+
+ <sect2 id="functions-datetime-extract">
+ <title><function>EXTRACT</function>, <function>date_part</function></title>
+
+ <indexterm>
+ <primary>date_part</primary>
+ </indexterm>
+ <indexterm>
+ <primary>extract</primary>
+ </indexterm>
+
+<synopsis>
+EXTRACT(<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
+</synopsis>
+
+ <para>
+ The <function>extract</function> function retrieves subfields
+ such as year or hour from date/time values.
+ <replaceable>source</replaceable> must be a value expression of
+ type <type>timestamp</type>, <type>date</type>, <type>time</type>,
+ or <type>interval</type>. (Timestamps and times can be with or
+ without time zone.)
+ <replaceable>field</replaceable> is an identifier or
+ string that selects what field to extract from the source value.
+ Not all fields are valid for every input data type; for example, fields
+ smaller than a day cannot be extracted from a <type>date</type>, while
+ fields of a day or more cannot be extracted from a <type>time</type>.
+ The <function>extract</function> function returns values of type
+ <type>numeric</type>.
+ </para>
+
+ <para>
+ The following are valid field names:
+
+ <!-- alphabetical -->
+ <variablelist>
+ <varlistentry>
+ <term><literal>century</literal></term>
+ <listitem>
+ <para>
+ The century; for <type>interval</type> values, the year field
+ divided by 100
+ </para>
+
+<screen>
+SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
+<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
+SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>21</computeroutput>
+SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');
+<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
+SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');
+<lineannotation>Result: </lineannotation><computeroutput>-1</computeroutput>
+SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years');
+<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>day</literal></term>
+ <listitem>
+ <para>
+ The day of the month (1–31); for <type>interval</type>
+ values, the number of days
+ </para>
+
+<screen>
+SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
+SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
+<lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
+</screen>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>decade</literal></term>
+ <listitem>
+ <para>
+ The year field divided by 10
+ </para>
+
+<screen>
+SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>200</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>dow</literal></term>
+ <listitem>
+ <para>
+ The day of the week as Sunday (<literal>0</literal>) to
+ Saturday (<literal>6</literal>)
+ </para>
+
+<screen>
+SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>
+</screen>
+ <para>
+ Note that <function>extract</function>'s day of the week numbering
+ differs from that of the <function>to_char(...,
+ 'D')</function> function.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>doy</literal></term>
+ <listitem>
+ <para>
+ The day of the year (1–365/366)
+ </para>
+
+<screen>
+SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>47</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>epoch</literal></term>
+ <listitem>
+ <para>
+ For <type>timestamp with time zone</type> values, the
+ 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
+ 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>
+
+<screen>
+SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
+<lineannotation>Result: </lineannotation><computeroutput>982384720.120000</computeroutput>
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
+<lineannotation>Result: </lineannotation><computeroutput>982355920.120000</computeroutput>
+SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
+<lineannotation>Result: </lineannotation><computeroutput>442800.000000</computeroutput>
+</screen>
+
+ <para>
+ 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>
+
+ <varlistentry>
+ <term><literal>hour</literal></term>
+ <listitem>
+ <para>
+ The hour field (0–23 in timestamps, unrestricted in
+ intervals)
+ </para>
+
+<screen>
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>isodow</literal></term>
+ <listitem>
+ <para>
+ The day of the week as Monday (<literal>1</literal>) to
+ Sunday (<literal>7</literal>)
+ </para>
+
+<screen>
+SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
+</screen>
+ <para>
+ This is identical to <literal>dow</literal> except for Sunday. This
+ matches the <acronym>ISO</acronym> 8601 day of the week numbering.
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>isoyear</literal></term>
+ <listitem>
+ <para>
+ The <acronym>ISO</acronym> 8601 week-numbering year that the date
+ falls in
+ </para>
+
+<screen>
+SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2005</computeroutput>
+SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
+<lineannotation>Result: </lineannotation><computeroutput>2006</computeroutput>
+</screen>
+
+ <para>
+ Each <acronym>ISO</acronym> 8601 week-numbering year begins with the
+ Monday of the week containing the 4th of January, so in early
+ January or late December the <acronym>ISO</acronym> year may be
+ different from the Gregorian year. See the <literal>week</literal>
+ field for more information.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>julian</literal></term>
+ <listitem>
+ <para>
+ The <firstterm>Julian Date</firstterm> corresponding to the
+ date or timestamp. Timestamps
+ that are not local midnight result in a fractional value. See
+ <xref linkend="datetime-julian-dates"/> for more information.
+ </para>
+
+<screen>
+SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2453737</computeroutput>
+SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
+<lineannotation>Result: </lineannotation><computeroutput>2453737.50000000000000000000</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>microseconds</literal></term>
+ <listitem>
+ <para>
+ The seconds field, including fractional parts, multiplied by 1
+ 000 000; note that this includes full seconds
+ </para>
+
+<screen>
+SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
+<lineannotation>Result: </lineannotation><computeroutput>28500000</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>millennium</literal></term>
+ <listitem>
+ <para>
+ The millennium; for <type>interval</type> values, the year field
+ divided by 1000
+ </para>
+
+<screen>
+SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
+SELECT EXTRACT(MILLENNIUM FROM INTERVAL '2001 years');
+<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
+</screen>
+
+ <para>
+ Years in the 1900s are in the second millennium.
+ The third millennium started January 1, 2001.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>milliseconds</literal></term>
+ <listitem>
+ <para>
+ The seconds field, including fractional parts, multiplied by
+ 1000. Note that this includes full seconds.
+ </para>
+
+<screen>
+SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
+<lineannotation>Result: </lineannotation><computeroutput>28500.000</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>minute</literal></term>
+ <listitem>
+ <para>
+ The minutes field (0–59)
+ </para>
+
+<screen>
+SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>38</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>month</literal></term>
+ <listitem>
+ <para>
+ The number of the month within the year (1–12);
+ for <type>interval</type> values, the number of months modulo 12
+ (0–11)
+ </para>
+
+<screen>
+SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
+SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
+<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
+SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
+<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>quarter</literal></term>
+ <listitem>
+ <para>
+ The quarter of the year (1–4) that the date is in
+ </para>
+
+<screen>
+SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>second</literal></term>
+ <listitem>
+ <para>
+ The seconds field, including any fractional seconds
+ </para>
+
+<screen>
+SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>40.000000</computeroutput>
+SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
+<lineannotation>Result: </lineannotation><computeroutput>28.500000</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>timezone</literal></term>
+ <listitem>
+ <para>
+ The time zone offset from UTC, measured in seconds. Positive values
+ correspond to time zones east of UTC, negative values to
+ zones west of UTC. (Technically,
+ <productname>PostgreSQL</productname> does not use UTC because
+ leap seconds are not handled.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>timezone_hour</literal></term>
+ <listitem>
+ <para>
+ The hour component of the time zone offset
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>timezone_minute</literal></term>
+ <listitem>
+ <para>
+ The minute component of the time zone offset
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>week</literal></term>
+ <listitem>
+ <para>
+ The number of the <acronym>ISO</acronym> 8601 week-numbering week of
+ the year. By definition, ISO weeks start on Mondays and the first
+ week of a year contains January 4 of that year. In other words, the
+ first Thursday of a year is in week 1 of that year.
+ </para>
+ <para>
+ In the ISO week-numbering system, it is possible for early-January
+ dates to be part of the 52nd or 53rd week of the previous year, and for
+ late-December dates to be part of the first week of the next year.
+ For example, <literal>2005-01-01</literal> is part of the 53rd week of year
+ 2004, and <literal>2006-01-01</literal> is part of the 52nd week of year
+ 2005, while <literal>2012-12-31</literal> is part of the first week of 2013.
+ It's recommended to use the <literal>isoyear</literal> field together with
+ <literal>week</literal> to get consistent results.
+ </para>
+
+<screen>
+SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>year</literal></term>
+ <listitem>
+ <para>
+ The year field. Keep in mind there is no <literal>0 AD</literal>, so subtracting
+ <literal>BC</literal> years from <literal>AD</literal> years should be done with care.
+ </para>
+
+<screen>
+SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+
+ <para>
+ When processing an <type>interval</type> value,
+ the <function>extract</function> function produces field values that
+ match the interpretation used by the interval output function. This
+ can produce surprising results if one starts with a non-normalized
+ interval representation, for example:
+<screen>
+SELECT INTERVAL '80 minutes';
+<lineannotation>Result: </lineannotation><computeroutput>01:20:00</computeroutput>
+SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
+<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
+</screen>
+ </para>
+
+ <note>
+ <para>
+ When the input value is +/-Infinity, <function>extract</function> returns
+ +/-Infinity for monotonically-increasing fields (<literal>epoch</literal>,
+ <literal>julian</literal>, <literal>year</literal>, <literal>isoyear</literal>,
+ <literal>decade</literal>, <literal>century</literal>, and <literal>millennium</literal>
+ for <type>timestamp</type> inputs; <literal>epoch</literal>, <literal>hour</literal>,
+ <literal>day</literal>, <literal>year</literal>, <literal>decade</literal>,
+ <literal>century</literal>, and <literal>millennium</literal> for
+ <type>interval</type> inputs).
+ For other fields, NULL is returned. <productname>PostgreSQL</productname>
+ versions before 9.6 returned zero for all cases of infinite input.
+ </para>
+ </note>
+
+ <para>
+ The <function>extract</function> function is primarily intended
+ for computational processing. For formatting date/time values for
+ display, see <xref linkend="functions-formatting"/>.
+ </para>
+
+ <para>
+ The <function>date_part</function> function is modeled on the traditional
+ <productname>Ingres</productname> equivalent to the
+ <acronym>SQL</acronym>-standard function <function>extract</function>:
+<synopsis>
+date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
+</synopsis>
+ Note that here the <replaceable>field</replaceable> parameter needs to
+ be a string value, not a name. The valid field names for
+ <function>date_part</function> are the same as for
+ <function>extract</function>.
+ For historical reasons, the <function>date_part</function> function
+ returns values of type <type>double precision</type>. This can result in
+ a loss of precision in certain uses. Using <function>extract</function>
+ is recommended instead.
+ </para>
+
+<screen>
+SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
+SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
+<lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
+</screen>
+
+ </sect2>
+
+ <sect2 id="functions-datetime-trunc">
+ <title><function>date_trunc</function></title>
+
+ <indexterm>
+ <primary>date_trunc</primary>
+ </indexterm>
+
+ <para>
+ The function <function>date_trunc</function> is conceptually
+ similar to the <function>trunc</function> function for numbers.
+ </para>
+
+ <para>
+<synopsis>
+date_trunc(<replaceable>field</replaceable>, <replaceable>source</replaceable> [, <replaceable>time_zone</replaceable> ])
+</synopsis>
+ <replaceable>source</replaceable> is a value expression of type
+ <type>timestamp</type>, <type>timestamp with time zone</type>,
+ or <type>interval</type>.
+ (Values of type <type>date</type> and
+ <type>time</type> are cast automatically to <type>timestamp</type> or
+ <type>interval</type>, respectively.)
+ <replaceable>field</replaceable> selects to which precision to
+ truncate the input value. The return value is likewise of type
+ <type>timestamp</type>, <type>timestamp with time zone</type>,
+ or <type>interval</type>,
+ and it has all fields that are less significant than the
+ selected one set to zero (or one, for day and month).
+ </para>
+
+ <para>
+ Valid values for <replaceable>field</replaceable> are:
+ <simplelist>
+ <member><literal>microseconds</literal></member>
+ <member><literal>milliseconds</literal></member>
+ <member><literal>second</literal></member>
+ <member><literal>minute</literal></member>
+ <member><literal>hour</literal></member>
+ <member><literal>day</literal></member>
+ <member><literal>week</literal></member>
+ <member><literal>month</literal></member>
+ <member><literal>quarter</literal></member>
+ <member><literal>year</literal></member>
+ <member><literal>decade</literal></member>
+ <member><literal>century</literal></member>
+ <member><literal>millennium</literal></member>
+ </simplelist>
+ </para>
+
+ <para>
+ When the input value is of type <type>timestamp with time zone</type>,
+ the truncation is performed with respect to a particular time zone;
+ for example, truncation to <literal>day</literal> produces a value that
+ is midnight in that zone. By default, truncation is done with respect
+ to the current <xref linkend="guc-timezone"/> setting, but the
+ optional <replaceable>time_zone</replaceable> argument can be provided
+ to specify a different time zone. The time zone name can be specified
+ in any of the ways described in <xref linkend="datatype-timezones"/>.
+ </para>
+
+ <para>
+ A time zone cannot be specified when processing <type>timestamp without
+ time zone</type> or <type>interval</type> inputs. These are always
+ taken at face value.
+ </para>
+
+ <para>
+ Examples (assuming the local time zone is <literal>America/New_York</literal>):
+<screen>
+SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>
+SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
+<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>
+SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 00:00:00-05</computeroutput>
+SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 08:00:00-05</computeroutput>
+SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
+<lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
+</screen>
+ </para>
+ </sect2>
+
+ <sect2 id="functions-datetime-bin">
+ <title><function>date_bin</function></title>
+
+ <indexterm>
+ <primary>date_bin</primary>
+ </indexterm>
+
+ <para>
+ The function <function>date_bin</function> <quote>bins</quote> the input
+ timestamp into the specified interval (the <firstterm>stride</firstterm>)
+ aligned with a specified origin.
+ </para>
+
+ <para>
+<synopsis>
+date_bin(<replaceable>stride</replaceable>, <replaceable>source</replaceable>, <replaceable>origin</replaceable>)
+</synopsis>
+ <replaceable>source</replaceable> is a value expression of type
+ <type>timestamp</type> or <type>timestamp with time zone</type>. (Values
+ of type <type>date</type> are cast automatically to
+ <type>timestamp</type>.) <replaceable>stride</replaceable> is a value
+ expression of type <type>interval</type>. The return value is likewise
+ of type <type>timestamp</type> or <type>timestamp with time zone</type>,
+ and it marks the beginning of the bin into which the
+ <replaceable>source</replaceable> is placed.
+ </para>
+
+ <para>
+ Examples:
+<screen>
+SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:30:00</computeroutput>
+SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
+<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:32:30</computeroutput>
+</screen>
+ </para>
+
+ <para>
+ In the case of full units (1 minute, 1 hour, etc.), it gives the same result as
+ the analogous <function>date_trunc</function> call, but the difference is
+ that <function>date_bin</function> can truncate to an arbitrary interval.
+ </para>
+
+ <para>
+ The <parameter>stride</parameter> interval must be greater than zero and
+ cannot contain units of month or larger.
+ </para>
+ </sect2>
+
+ <sect2 id="functions-datetime-zoneconvert">
+ <title><literal>AT TIME ZONE and AT LOCAL</literal></title>
+
+ <indexterm>
+ <primary>time zone</primary>
+ <secondary>conversion</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>AT TIME ZONE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>AT LOCAL</primary>
+ </indexterm>
+
+ <para>
+ The <literal>AT TIME ZONE</literal> operator converts time
+ stamp <emphasis>without</emphasis> time zone to/from
+ time stamp <emphasis>with</emphasis> time zone, and
+ <type>time with time zone</type> values to different time
+ zones. <xref linkend="functions-datetime-zoneconvert-table"/> shows its
+ variants.
+ </para>
+
+ <table id="functions-datetime-zoneconvert-table">
+ <title><literal>AT TIME ZONE</literal> and <literal>AT LOCAL</literal> Variants</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Operator
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>timestamp without time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Converts given time stamp <emphasis>without</emphasis> time zone to
+ time stamp <emphasis>with</emphasis> time zone, assuming the given
+ value is in the named time zone.
+ </para>
+ <para>
+ <literal>timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'</literal>
+ <returnvalue>2001-02-17 03:38:40+00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>timestamp without time zone</type> <literal>AT LOCAL</literal>
+ <returnvalue>timestamp with time zone</returnvalue>
+ </para>
+ <para>
+ Converts given time stamp <emphasis>without</emphasis> time zone to
+ time stamp <emphasis>with</emphasis> the session's
+ <varname>TimeZone</varname> value as time zone.
+ </para>
+ <para>
+ <literal>timestamp '2001-02-16 20:38:40' at local</literal>
+ <returnvalue>2001-02-17 03:38:40+00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>timestamp with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
+ <returnvalue>timestamp without time zone</returnvalue>
+ </para>
+ <para>
+ Converts given time stamp <emphasis>with</emphasis> time zone to
+ time stamp <emphasis>without</emphasis> time zone, as the time would
+ appear in that zone.
+ </para>
+ <para>
+ <literal>timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver'</literal>
+ <returnvalue>2001-02-16 18:38:40</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>timestamp with time zone</type> <literal>AT LOCAL</literal>
+ <returnvalue>timestamp without time zone</returnvalue>
+ </para>
+ <para>
+ Converts given time stamp <emphasis>with</emphasis> time zone to
+ time stamp <emphasis>without</emphasis> time zone, as the time would
+ appear with the session's <varname>TimeZone</varname> value as time zone.
+ </para>
+ <para>
+ <literal>timestamp with time zone '2001-02-16 20:38:40-05' at local</literal>
+ <returnvalue>2001-02-16 18:38:40</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>time with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
+ <returnvalue>time with time zone</returnvalue>
+ </para>
+ <para>
+ Converts given time <emphasis>with</emphasis> time zone to a new time
+ zone. Since no date is supplied, this uses the currently active UTC
+ offset for the named destination zone.
+ </para>
+ <para>
+ <literal>time with time zone '05:34:17-05' at time zone 'UTC'</literal>
+ <returnvalue>10:34:17+00</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>time with time zone</type> <literal>AT LOCAL</literal>
+ <returnvalue>time with time zone</returnvalue>
+ </para>
+ <para>
+ Converts given time <emphasis>with</emphasis> time zone to a new time
+ zone. Since no date is supplied, this uses the currently active UTC
+ offset for the session's <varname>TimeZone</varname> value.
+ </para>
+ <para>
+ Assuming the session's <varname>TimeZone</varname> is set to <literal>UTC</literal>:
+ </para>
+ <para>
+ <literal>time with time zone '05:34:17-05' at local</literal>
+ <returnvalue>10:34:17+00</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ In these expressions, the desired time zone <replaceable>zone</replaceable> can be
+ specified either as a text value (e.g., <literal>'America/Los_Angeles'</literal>)
+ or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
+ In the text case, a time zone name can be specified in any of the ways
+ described in <xref linkend="datatype-timezones"/>.
+ The interval case is only useful for zones that have fixed offsets from
+ UTC, so it is not very common in practice.
+ </para>
+
+ <para>
+ The syntax <literal>AT LOCAL</literal> may be used as shorthand for
+ <literal>AT TIME ZONE <replaceable>local</replaceable></literal>, where
+ <replaceable>local</replaceable> is the session's
+ <varname>TimeZone</varname> value.
+ </para>
+
+ <para>
+ Examples (assuming the current <xref linkend="guc-timezone"/> setting
+ is <literal>America/Los_Angeles</literal>):
+<screen>
+SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>
+SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
+SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 05:38:40</computeroutput>
+SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 17:38:40</computeroutput>
+SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;
+<lineannotation>Result: </lineannotation><computeroutput>17:38:40</computeroutput>
+</screen>
+ The first example adds a time zone to a value that lacks it, and
+ displays the value using the current <varname>TimeZone</varname>
+ setting. The second example shifts the time stamp with time zone value
+ to the specified time zone, and returns the value without a time zone.
+ This allows storage and display of values different from the current
+ <varname>TimeZone</varname> setting. The third example converts
+ Tokyo time to Chicago time. The fourth example shifts the time stamp
+ with time zone value to the time zone currently specified by the
+ <varname>TimeZone</varname> setting and returns the value without a
+ time zone.
+ </para>
+
+ <para>
+ The fifth example is a cautionary tale. Due to the fact that there is no
+ date associated with the input value, the conversion is made using the
+ current date of the session. Therefore, this static example may show a wrong
+ result depending on the time of the year it is viewed because
+ <literal>'America/Los_Angeles'</literal> observes Daylight Savings Time.
+ </para>
+
+ <para>
+ The function <literal><function>timezone</function>(<replaceable>zone</replaceable>,
+ <replaceable>timestamp</replaceable>)</literal> is equivalent to the SQL-conforming construct
+ <literal><replaceable>timestamp</replaceable> AT TIME ZONE
+ <replaceable>zone</replaceable></literal>.
+ </para>
+
+ <para>
+ The function <literal><function>timezone</function>(<replaceable>zone</replaceable>,
+ <replaceable>time</replaceable>)</literal> is equivalent to the SQL-conforming construct
+ <literal><replaceable>time</replaceable> AT TIME ZONE
+ <replaceable>zone</replaceable></literal>.
+ </para>
+
+ <para>
+ The function <literal><function>timezone</function>(<replaceable>timestamp</replaceable>)</literal>
+ is equivalent to the SQL-conforming construct <literal><replaceable>timestamp</replaceable>
+ AT LOCAL</literal>.
+ </para>
+
+ <para>
+ The function <literal><function>timezone</function>(<replaceable>time</replaceable>)</literal>
+ is equivalent to the SQL-conforming construct <literal><replaceable>time</replaceable>
+ AT LOCAL</literal>.
+ </para>
+ </sect2>
+
+ <sect2 id="functions-datetime-current">
+ <title>Current Date/Time</title>
+
+ <indexterm>
+ <primary>date</primary>
+ <secondary>current</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>time</primary>
+ <secondary>current</secondary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> provides a number of functions
+ that return values related to the current date and time. These
+ SQL-standard functions all return values based on the start time of
+ the current transaction:
+<synopsis>
+CURRENT_DATE
+CURRENT_TIME
+CURRENT_TIMESTAMP
+CURRENT_TIME(<replaceable>precision</replaceable>)
+CURRENT_TIMESTAMP(<replaceable>precision</replaceable>)
+LOCALTIME
+LOCALTIMESTAMP
+LOCALTIME(<replaceable>precision</replaceable>)
+LOCALTIMESTAMP(<replaceable>precision</replaceable>)
+</synopsis>
+ </para>
+
+ <para>
+ <function>CURRENT_TIME</function> and
+ <function>CURRENT_TIMESTAMP</function> deliver values with time zone;
+ <function>LOCALTIME</function> and
+ <function>LOCALTIMESTAMP</function> deliver values without time zone.
+ </para>
+
+ <para>
+ <function>CURRENT_TIME</function>,
+ <function>CURRENT_TIMESTAMP</function>,
+ <function>LOCALTIME</function>, and
+ <function>LOCALTIMESTAMP</function>
+ can optionally take
+ a precision parameter, which causes the result to be rounded
+ to that many fractional digits in the seconds field. Without a precision parameter,
+ the result is given to the full available precision.
+ </para>
+
+ <para>
+ Some examples:
+<screen>
+SELECT CURRENT_TIME;
+<lineannotation>Result: </lineannotation><computeroutput>14:39:53.662522-05</computeroutput>
+SELECT CURRENT_DATE;
+<lineannotation>Result: </lineannotation><computeroutput>2019-12-23</computeroutput>
+SELECT CURRENT_TIMESTAMP;
+<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522-05</computeroutput>
+SELECT CURRENT_TIMESTAMP(2);
+<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.66-05</computeroutput>
+SELECT LOCALTIMESTAMP;
+<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522</computeroutput>
+</screen>
+ </para>
+
+ <para>
+ Since these functions return
+ the start time of the current transaction, their values do not
+ change during the transaction. This is considered a feature:
+ the intent is to allow a single transaction to have a consistent
+ notion of the <quote>current</quote> time, so that multiple
+ modifications within the same transaction bear the same
+ time stamp.
+ </para>
+
+ <note>
+ <para>
+ Other database systems might advance these values more
+ frequently.
+ </para>
+ </note>
+
+ <para>
+ <productname>PostgreSQL</productname> also provides functions that
+ return the start time of the current statement, as well as the actual
+ current time at the instant the function is called. The complete list
+ of non-SQL-standard time functions is:
+<synopsis>
+transaction_timestamp()
+statement_timestamp()
+clock_timestamp()
+timeofday()
+now()
+</synopsis>
+ </para>
+
+ <para>
+ <function>transaction_timestamp()</function> is equivalent to
+ <function>CURRENT_TIMESTAMP</function>, but is named to clearly reflect
+ what it returns.
+ <function>statement_timestamp()</function> returns the start time of the current
+ statement (more specifically, the time of receipt of the latest command
+ message from the client).
+ <function>statement_timestamp()</function> and <function>transaction_timestamp()</function>
+ return the same value during the first command of a transaction, but might
+ differ during subsequent commands.
+ <function>clock_timestamp()</function> returns the actual current time, and
+ therefore its value changes even within a single SQL command.
+ <function>timeofday()</function> is a historical
+ <productname>PostgreSQL</productname> function. Like
+ <function>clock_timestamp()</function>, it returns the actual current time,
+ but as a formatted <type>text</type> string rather than a <type>timestamp
+ with time zone</type> value.
+ <function>now()</function> is a traditional <productname>PostgreSQL</productname>
+ equivalent to <function>transaction_timestamp()</function>.
+ </para>
+
+ <para>
+ All the date/time data types also accept the special literal value
+ <literal>now</literal> to specify the current date and time (again,
+ interpreted as the transaction start time). Thus,
+ the following three all return the same result:
+<programlisting>
+SELECT CURRENT_TIMESTAMP;
+SELECT now();
+SELECT TIMESTAMP 'now'; -- but see tip below
+</programlisting>
+ </para>
+
+ <tip>
+ <para>
+ Do not use the third form when specifying a value to be evaluated later,
+ for example in a <literal>DEFAULT</literal> clause for a table column.
+ The system will convert <literal>now</literal>
+ to a <type>timestamp</type> as soon as the constant is parsed, so that when
+ the default value is needed,
+ the time of the table creation would be used! The first two
+ forms will not be evaluated until the default value is used,
+ because they are function calls. Thus they will give the desired
+ behavior of defaulting to the time of row insertion.
+ (See also <xref linkend="datatype-datetime-special-values"/>.)
+ </para>
+ </tip>
+ </sect2>
+
+ <sect2 id="functions-datetime-delay">
+ <title>Delaying Execution</title>
+
+ <indexterm>
+ <primary>pg_sleep</primary>
+ </indexterm>
+ <indexterm>
+ <primary>pg_sleep_for</primary>
+ </indexterm>
+ <indexterm>
+ <primary>pg_sleep_until</primary>
+ </indexterm>
+ <indexterm>
+ <primary>sleep</primary>
+ </indexterm>
+ <indexterm>
+ <primary>delay</primary>
+ </indexterm>
+
+ <para>
+ The following functions are available to delay execution of the server
+ process:
+<synopsis>
+pg_sleep ( <type>double precision</type> )
+pg_sleep_for ( <type>interval</type> )
+pg_sleep_until ( <type>timestamp with time zone</type> )
+</synopsis>
+
+ <function>pg_sleep</function> makes the current session's process
+ sleep until the given number of seconds have
+ elapsed. Fractional-second delays can be specified.
+ <function>pg_sleep_for</function> is a convenience function to
+ allow the sleep time to be specified as an <type>interval</type>.
+ <function>pg_sleep_until</function> is a convenience function for when
+ a specific wake-up time is desired.
+ For example:
+
+<programlisting>
+SELECT pg_sleep(1.5);
+SELECT pg_sleep_for('5 minutes');
+SELECT pg_sleep_until('tomorrow 03:00');
+</programlisting>
+ </para>
+
+ <note>
+ <para>
+ The effective resolution of the sleep interval is platform-specific;
+ 0.01 seconds is a common value. The sleep delay will be at least as long
+ as specified. It might be longer depending on factors such as server load.
+ In particular, <function>pg_sleep_until</function> is not guaranteed to
+ wake up exactly at the specified time, but it will not wake up any earlier.
+ </para>
+ </note>
+
+ <warning>
+ <para>
+ Make sure that your session does not hold more locks than necessary
+ when calling <function>pg_sleep</function> or its variants. Otherwise
+ other sessions might have to wait for your sleeping process, slowing down
+ the entire system.
+ </para>
+ </warning>
+ </sect2>
+
+ </sect1>
\ No newline at end of file
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a49f212d..ca33edc7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -4735,2192 +4735,7 @@ cast(-44 as bit(12)) <lineannotation>111111010100</lineannotation>
</sect1>
-
- <sect1 id="functions-datetime">
- <title>Date/Time Functions and Operators</title>
-
- <para>
- <xref linkend="functions-datetime-table"/> shows the available
- functions for date/time value processing, with details appearing in
- the following subsections. <xref
- linkend="operators-datetime-table"/> illustrates the behaviors of
- the basic arithmetic operators (<literal>+</literal>,
- <literal>*</literal>, etc.). For formatting functions, refer to
- <xref linkend="functions-formatting"/>. You should be familiar with
- the background information on date/time data types from <xref
- linkend="datatype-datetime"/>.
- </para>
-
- <para>
- In addition, the usual comparison operators shown in
- <xref linkend="functions-comparison-op-table"/> are available for the
- date/time types. Dates and timestamps (with or without time zone) are
- all comparable, while times (with or without time zone) and intervals
- can only be compared to other values of the same data type. When
- comparing a timestamp without time zone to a timestamp with time zone,
- the former value is assumed to be given in the time zone specified by
- the <xref linkend="guc-timezone"/> configuration parameter, and is
- rotated to UTC for comparison to the latter value (which is already
- in UTC internally). Similarly, a date value is assumed to represent
- midnight in the <varname>TimeZone</varname> zone when comparing it
- to a timestamp.
- </para>
-
- <para>
- All the functions and operators described below that take <type>time</type> or <type>timestamp</type>
- inputs actually come in two variants: one that takes <type>time with time zone</type> or <type>timestamp
- with time zone</type>, and one that takes <type>time without time zone</type> or <type>timestamp without time zone</type>.
- For brevity, these variants are not shown separately. Also, the
- <literal>+</literal> and <literal>*</literal> operators come in commutative pairs (for
- example both <type>date</type> <literal>+</literal> <type>integer</type>
- and <type>integer</type> <literal>+</literal> <type>date</type>); we show
- only one of each such pair.
- </para>
-
- <table id="operators-datetime-table">
- <title>Date/Time Operators</title>
-
- <tgroup cols="1">
- <thead>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- Operator
- </para>
- <para>
- Description
- </para>
- <para>
- Example(s)
- </para></entry>
- </row>
- </thead>
-
- <tbody>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>date</type> <literal>+</literal> <type>integer</type>
- <returnvalue>date</returnvalue>
- </para>
- <para>
- Add a number of days to a date
- </para>
- <para>
- <literal>date '2001-09-28' + 7</literal>
- <returnvalue>2001-10-05</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>date</type> <literal>+</literal> <type>interval</type>
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Add an interval to a date
- </para>
- <para>
- <literal>date '2001-09-28' + interval '1 hour'</literal>
- <returnvalue>2001-09-28 01:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>date</type> <literal>+</literal> <type>time</type>
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Add a time-of-day to a date
- </para>
- <para>
- <literal>date '2001-09-28' + time '03:00'</literal>
- <returnvalue>2001-09-28 03:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>interval</type> <literal>+</literal> <type>interval</type>
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Add intervals
- </para>
- <para>
- <literal>interval '1 day' + interval '1 hour'</literal>
- <returnvalue>1 day 01:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>timestamp</type> <literal>+</literal> <type>interval</type>
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Add an interval to a timestamp
- </para>
- <para>
- <literal>timestamp '2001-09-28 01:00' + interval '23 hours'</literal>
- <returnvalue>2001-09-29 00:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>time</type> <literal>+</literal> <type>interval</type>
- <returnvalue>time</returnvalue>
- </para>
- <para>
- Add an interval to a time
- </para>
- <para>
- <literal>time '01:00' + interval '3 hours'</literal>
- <returnvalue>04:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <literal>-</literal> <type>interval</type>
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Negate an interval
- </para>
- <para>
- <literal>- interval '23 hours'</literal>
- <returnvalue>-23:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>date</type> <literal>-</literal> <type>date</type>
- <returnvalue>integer</returnvalue>
- </para>
- <para>
- Subtract dates, producing the number of days elapsed
- </para>
- <para>
- <literal>date '2001-10-01' - date '2001-09-28'</literal>
- <returnvalue>3</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>date</type> <literal>-</literal> <type>integer</type>
- <returnvalue>date</returnvalue>
- </para>
- <para>
- Subtract a number of days from a date
- </para>
- <para>
- <literal>date '2001-10-01' - 7</literal>
- <returnvalue>2001-09-24</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>date</type> <literal>-</literal> <type>interval</type>
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Subtract an interval from a date
- </para>
- <para>
- <literal>date '2001-09-28' - interval '1 hour'</literal>
- <returnvalue>2001-09-27 23:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>time</type> <literal>-</literal> <type>time</type>
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Subtract times
- </para>
- <para>
- <literal>time '05:00' - time '03:00'</literal>
- <returnvalue>02:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>time</type> <literal>-</literal> <type>interval</type>
- <returnvalue>time</returnvalue>
- </para>
- <para>
- Subtract an interval from a time
- </para>
- <para>
- <literal>time '05:00' - interval '2 hours'</literal>
- <returnvalue>03:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>timestamp</type> <literal>-</literal> <type>interval</type>
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Subtract an interval from a timestamp
- </para>
- <para>
- <literal>timestamp '2001-09-28 23:00' - interval '23 hours'</literal>
- <returnvalue>2001-09-28 00:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>interval</type> <literal>-</literal> <type>interval</type>
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Subtract intervals
- </para>
- <para>
- <literal>interval '1 day' - interval '1 hour'</literal>
- <returnvalue>1 day -01:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>timestamp</type> <literal>-</literal> <type>timestamp</type>
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Subtract timestamps (converting 24-hour intervals into days,
- similarly to <link
- linkend="function-justify-hours"><function>justify_hours()</function></link>)
- </para>
- <para>
- <literal>timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'</literal>
- <returnvalue>63 days 15:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>interval</type> <literal>*</literal> <type>double precision</type>
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Multiply an interval by a scalar
- </para>
- <para>
- <literal>interval '1 second' * 900</literal>
- <returnvalue>00:15:00</returnvalue>
- </para>
- <para>
- <literal>interval '1 day' * 21</literal>
- <returnvalue>21 days</returnvalue>
- </para>
- <para>
- <literal>interval '1 hour' * 3.5</literal>
- <returnvalue>03:30:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>interval</type> <literal>/</literal> <type>double precision</type>
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Divide an interval by a scalar
- </para>
- <para>
- <literal>interval '1 hour' / 1.5</literal>
- <returnvalue>00:40:00</returnvalue>
- </para></entry>
- </row>
- </tbody>
- </tgroup>
- </table>
-
- <table id="functions-datetime-table">
- <title>Date/Time Functions</title>
- <tgroup cols="1">
- <thead>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- Function
- </para>
- <para>
- Description
- </para>
- <para>
- Example(s)
- </para></entry>
- </row>
- </thead>
-
- <tbody>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>age</primary>
- </indexterm>
- <function>age</function> ( <type>timestamp</type>, <type>timestamp</type> )
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Subtract arguments, producing a <quote>symbolic</quote> result that
- uses years and months, rather than just days
- </para>
- <para>
- <literal>age(timestamp '2001-04-10', timestamp '1957-06-13')</literal>
- <returnvalue>43 years 9 mons 27 days</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>age</function> ( <type>timestamp</type> )
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Subtract argument from <function>current_date</function> (at midnight)
- </para>
- <para>
- <literal>age(timestamp '1957-06-13')</literal>
- <returnvalue>62 years 6 mons 10 days</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>clock_timestamp</primary>
- </indexterm>
- <function>clock_timestamp</function> ( )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Current date and time (changes during statement execution);
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>clock_timestamp()</literal>
- <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>current_date</primary>
- </indexterm>
- <function>current_date</function>
- <returnvalue>date</returnvalue>
- </para>
- <para>
- Current date; see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>current_date</literal>
- <returnvalue>2019-12-23</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>current_time</primary>
- </indexterm>
- <function>current_time</function>
- <returnvalue>time with time zone</returnvalue>
- </para>
- <para>
- Current time of day; see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>current_time</literal>
- <returnvalue>14:39:53.662522-05</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>current_time</function> ( <type>integer</type> )
- <returnvalue>time with time zone</returnvalue>
- </para>
- <para>
- Current time of day, with limited precision;
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>current_time(2)</literal>
- <returnvalue>14:39:53.66-05</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>current_timestamp</primary>
- </indexterm>
- <function>current_timestamp</function>
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Current date and time (start of current transaction);
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>current_timestamp</literal>
- <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>current_timestamp</function> ( <type>integer</type> )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Current date and time (start of current transaction), with limited precision;
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>current_timestamp(0)</literal>
- <returnvalue>2019-12-23 14:39:53-05</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>date_add</primary>
- </indexterm>
- <function>date_add</function> ( <type>timestamp with time zone</type>, <type>interval</type> <optional>, <type>text</type> </optional> )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Add an <type>interval</type> to a <type>timestamp with time
- zone</type>, computing times of day and daylight-savings adjustments
- according to the time zone named by the third argument, or the
- current <xref linkend="guc-timezone"/> setting if that is omitted.
- The form with two arguments is equivalent to the <type>timestamp with
- time zone</type> <literal>+</literal> <type>interval</type> operator.
- </para>
- <para>
- <literal>date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</literal>
- <returnvalue>2021-10-31 23:00:00+00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>date_bin</function> ( <type>interval</type>, <type>timestamp</type>, <type>timestamp</type> )
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Bin input into specified interval aligned with specified origin; see <xref linkend="functions-datetime-bin"/>
- </para>
- <para>
- <literal>date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</literal>
- <returnvalue>2001-02-16 20:35:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>date_part</primary>
- </indexterm>
- <function>date_part</function> ( <type>text</type>, <type>timestamp</type> )
- <returnvalue>double precision</returnvalue>
- </para>
- <para>
- Get timestamp subfield (equivalent to <function>extract</function>);
- see <xref linkend="functions-datetime-extract"/>
- </para>
- <para>
- <literal>date_part('hour', timestamp '2001-02-16 20:38:40')</literal>
- <returnvalue>20</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>date_part</function> ( <type>text</type>, <type>interval</type> )
- <returnvalue>double precision</returnvalue>
- </para>
- <para>
- Get interval subfield (equivalent to <function>extract</function>);
- see <xref linkend="functions-datetime-extract"/>
- </para>
- <para>
- <literal>date_part('month', interval '2 years 3 months')</literal>
- <returnvalue>3</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>date_subtract</primary>
- </indexterm>
- <function>date_subtract</function> ( <type>timestamp with time zone</type>, <type>interval</type> <optional>, <type>text</type> </optional> )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Subtract an <type>interval</type> from a <type>timestamp with time
- zone</type>, computing times of day and daylight-savings adjustments
- according to the time zone named by the third argument, or the
- current <xref linkend="guc-timezone"/> setting if that is omitted.
- The form with two arguments is equivalent to the <type>timestamp with
- time zone</type> <literal>-</literal> <type>interval</type> operator.
- </para>
- <para>
- <literal>date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</literal>
- <returnvalue>2021-10-30 22:00:00+00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>date_trunc</primary>
- </indexterm>
- <function>date_trunc</function> ( <type>text</type>, <type>timestamp</type> )
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Truncate to specified precision; see <xref linkend="functions-datetime-trunc"/>
- </para>
- <para>
- <literal>date_trunc('hour', timestamp '2001-02-16 20:38:40')</literal>
- <returnvalue>2001-02-16 20:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>date_trunc</function> ( <type>text</type>, <type>timestamp with time zone</type>, <type>text</type> )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Truncate to specified precision in the specified time zone; see
- <xref linkend="functions-datetime-trunc"/>
- </para>
- <para>
- <literal>date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')</literal>
- <returnvalue>2001-02-16 13:00:00+00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>date_trunc</function> ( <type>text</type>, <type>interval</type> )
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Truncate to specified precision; see
- <xref linkend="functions-datetime-trunc"/>
- </para>
- <para>
- <literal>date_trunc('hour', interval '2 days 3 hours 40 minutes')</literal>
- <returnvalue>2 days 03:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>extract</primary>
- </indexterm>
- <function>extract</function> ( <parameter>field</parameter> <literal>from</literal> <type>timestamp</type> )
- <returnvalue>numeric</returnvalue>
- </para>
- <para>
- Get timestamp subfield; see <xref linkend="functions-datetime-extract"/>
- </para>
- <para>
- <literal>extract(hour from timestamp '2001-02-16 20:38:40')</literal>
- <returnvalue>20</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>extract</function> ( <parameter>field</parameter> <literal>from</literal> <type>interval</type> )
- <returnvalue>numeric</returnvalue>
- </para>
- <para>
- Get interval subfield; see <xref linkend="functions-datetime-extract"/>
- </para>
- <para>
- <literal>extract(month from interval '2 years 3 months')</literal>
- <returnvalue>3</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>isfinite</primary>
- </indexterm>
- <function>isfinite</function> ( <type>date</type> )
- <returnvalue>boolean</returnvalue>
- </para>
- <para>
- Test for finite date (not +/-infinity)
- </para>
- <para>
- <literal>isfinite(date '2001-02-16')</literal>
- <returnvalue>true</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>isfinite</function> ( <type>timestamp</type> )
- <returnvalue>boolean</returnvalue>
- </para>
- <para>
- Test for finite timestamp (not +/-infinity)
- </para>
- <para>
- <literal>isfinite(timestamp 'infinity')</literal>
- <returnvalue>false</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>isfinite</function> ( <type>interval</type> )
- <returnvalue>boolean</returnvalue>
- </para>
- <para>
- Test for finite interval (not +/-infinity)
- </para>
- <para>
- <literal>isfinite(interval '4 hours')</literal>
- <returnvalue>true</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm id="function-justify-days">
- <primary>justify_days</primary>
- </indexterm>
- <function>justify_days</function> ( <type>interval</type> )
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Adjust interval, converting 30-day time periods to months
- </para>
- <para>
- <literal>justify_days(interval '1 year 65 days')</literal>
- <returnvalue>1 year 2 mons 5 days</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm id="function-justify-hours">
- <primary>justify_hours</primary>
- </indexterm>
- <function>justify_hours</function> ( <type>interval</type> )
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Adjust interval, converting 24-hour time periods to days
- </para>
- <para>
- <literal>justify_hours(interval '50 hours 10 minutes')</literal>
- <returnvalue>2 days 02:10:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>justify_interval</primary>
- </indexterm>
- <function>justify_interval</function> ( <type>interval</type> )
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Adjust interval using <function>justify_days</function>
- and <function>justify_hours</function>, with additional sign
- adjustments
- </para>
- <para>
- <literal>justify_interval(interval '1 mon -1 hour')</literal>
- <returnvalue>29 days 23:00:00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>localtime</primary>
- </indexterm>
- <function>localtime</function>
- <returnvalue>time</returnvalue>
- </para>
- <para>
- Current time of day;
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>localtime</literal>
- <returnvalue>14:39:53.662522</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>localtime</function> ( <type>integer</type> )
- <returnvalue>time</returnvalue>
- </para>
- <para>
- Current time of day, with limited precision;
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>localtime(0)</literal>
- <returnvalue>14:39:53</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>localtimestamp</primary>
- </indexterm>
- <function>localtimestamp</function>
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Current date and time (start of current transaction);
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>localtimestamp</literal>
- <returnvalue>2019-12-23 14:39:53.662522</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <function>localtimestamp</function> ( <type>integer</type> )
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Current date and time (start of current
- transaction), with limited precision;
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>localtimestamp(2)</literal>
- <returnvalue>2019-12-23 14:39:53.66</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>make_date</primary>
- </indexterm>
- <function>make_date</function> ( <parameter>year</parameter> <type>int</type>,
- <parameter>month</parameter> <type>int</type>,
- <parameter>day</parameter> <type>int</type> )
- <returnvalue>date</returnvalue>
- </para>
- <para>
- Create date from year, month and day fields
- (negative years signify BC)
- </para>
- <para>
- <literal>make_date(2013, 7, 15)</literal>
- <returnvalue>2013-07-15</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature"><indexterm>
- <primary>make_interval</primary>
- </indexterm>
- <function>make_interval</function> ( <optional> <parameter>years</parameter> <type>int</type>
- <optional>, <parameter>months</parameter> <type>int</type>
- <optional>, <parameter>weeks</parameter> <type>int</type>
- <optional>, <parameter>days</parameter> <type>int</type>
- <optional>, <parameter>hours</parameter> <type>int</type>
- <optional>, <parameter>mins</parameter> <type>int</type>
- <optional>, <parameter>secs</parameter> <type>double precision</type>
- </optional></optional></optional></optional></optional></optional></optional> )
- <returnvalue>interval</returnvalue>
- </para>
- <para>
- Create interval from years, months, weeks, days, hours, minutes and
- seconds fields, each of which can default to zero
- </para>
- <para>
- <literal>make_interval(days => 10)</literal>
- <returnvalue>10 days</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>make_time</primary>
- </indexterm>
- <function>make_time</function> ( <parameter>hour</parameter> <type>int</type>,
- <parameter>min</parameter> <type>int</type>,
- <parameter>sec</parameter> <type>double precision</type> )
- <returnvalue>time</returnvalue>
- </para>
- <para>
- Create time from hour, minute and seconds fields
- </para>
- <para>
- <literal>make_time(8, 15, 23.5)</literal>
- <returnvalue>08:15:23.5</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>make_timestamp</primary>
- </indexterm>
- <function>make_timestamp</function> ( <parameter>year</parameter> <type>int</type>,
- <parameter>month</parameter> <type>int</type>,
- <parameter>day</parameter> <type>int</type>,
- <parameter>hour</parameter> <type>int</type>,
- <parameter>min</parameter> <type>int</type>,
- <parameter>sec</parameter> <type>double precision</type> )
- <returnvalue>timestamp</returnvalue>
- </para>
- <para>
- Create timestamp from year, month, day, hour, minute and seconds fields
- (negative years signify BC)
- </para>
- <para>
- <literal>make_timestamp(2013, 7, 15, 8, 15, 23.5)</literal>
- <returnvalue>2013-07-15 08:15:23.5</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>make_timestamptz</primary>
- </indexterm>
- <function>make_timestamptz</function> ( <parameter>year</parameter> <type>int</type>,
- <parameter>month</parameter> <type>int</type>,
- <parameter>day</parameter> <type>int</type>,
- <parameter>hour</parameter> <type>int</type>,
- <parameter>min</parameter> <type>int</type>,
- <parameter>sec</parameter> <type>double precision</type>
- <optional>, <parameter>timezone</parameter> <type>text</type> </optional> )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Create timestamp with time zone from year, month, day, hour, minute
- and seconds fields (negative years signify BC).
- If <parameter>timezone</parameter> is not
- specified, the current time zone is used; the examples assume the
- session time zone is <literal>Europe/London</literal>
- </para>
- <para>
- <literal>make_timestamptz(2013, 7, 15, 8, 15, 23.5)</literal>
- <returnvalue>2013-07-15 08:15:23.5+01</returnvalue>
- </para>
- <para>
- <literal>make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York')</literal>
- <returnvalue>2013-07-15 13:15:23.5+01</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>now</primary>
- </indexterm>
- <function>now</function> ( )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Current date and time (start of current transaction);
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>now()</literal>
- <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>statement_timestamp</primary>
- </indexterm>
- <function>statement_timestamp</function> ( )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Current date and time (start of current statement);
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>statement_timestamp()</literal>
- <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>timeofday</primary>
- </indexterm>
- <function>timeofday</function> ( )
- <returnvalue>text</returnvalue>
- </para>
- <para>
- Current date and time
- (like <function>clock_timestamp</function>, but as a <type>text</type> string);
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>timeofday()</literal>
- <returnvalue>Mon Dec 23 14:39:53.662522 2019 EST</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>transaction_timestamp</primary>
- </indexterm>
- <function>transaction_timestamp</function> ( )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Current date and time (start of current transaction);
- see <xref linkend="functions-datetime-current"/>
- </para>
- <para>
- <literal>transaction_timestamp()</literal>
- <returnvalue>2019-12-23 14:39:53.662522-05</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <indexterm>
- <primary>to_timestamp</primary>
- </indexterm>
- <function>to_timestamp</function> ( <type>double precision</type> )
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to
- timestamp with time zone
- </para>
- <para>
- <literal>to_timestamp(1284352323)</literal>
- <returnvalue>2010-09-13 04:32:03+00</returnvalue>
- </para></entry>
- </row>
- </tbody>
- </tgroup>
- </table>
-
- <para>
- <indexterm>
- <primary>OVERLAPS</primary>
- </indexterm>
- In addition to these functions, the SQL <literal>OVERLAPS</literal> operator is
- supported:
-<synopsis>
-(<replaceable>start1</replaceable>, <replaceable>end1</replaceable>) OVERLAPS (<replaceable>start2</replaceable>, <replaceable>end2</replaceable>)
-(<replaceable>start1</replaceable>, <replaceable>length1</replaceable>) OVERLAPS (<replaceable>start2</replaceable>, <replaceable>length2</replaceable>)
-</synopsis>
- This expression yields true when two time periods (defined by their
- endpoints) overlap, false when they do not overlap. The endpoints
- can be specified as pairs of dates, times, or time stamps; or as
- a date, time, or time stamp followed by an interval. When a pair
- of values is provided, either the start or the end can be written
- first; <literal>OVERLAPS</literal> automatically takes the earlier value
- of the pair as the start. Each time period is considered to
- represent the half-open interval <replaceable>start</replaceable> <literal><=</literal>
- <replaceable>time</replaceable> <literal><</literal> <replaceable>end</replaceable>, unless
- <replaceable>start</replaceable> and <replaceable>end</replaceable> are equal in which case it
- represents that single time instant. This means for instance that two
- time periods with only an endpoint in common do not overlap.
- </para>
-
-<screen>
-SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
- (DATE '2001-10-30', DATE '2002-10-30');
-<lineannotation>Result: </lineannotation><computeroutput>true</computeroutput>
-SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
- (DATE '2001-10-30', DATE '2002-10-30');
-<lineannotation>Result: </lineannotation><computeroutput>false</computeroutput>
-SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
- (DATE '2001-10-30', DATE '2001-10-31');
-<lineannotation>Result: </lineannotation><computeroutput>false</computeroutput>
-SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
- (DATE '2001-10-30', DATE '2001-10-31');
-<lineannotation>Result: </lineannotation><computeroutput>true</computeroutput>
-</screen>
-
- <para>
- When adding an <type>interval</type> value to (or subtracting an
- <type>interval</type> value from) a <type>timestamp</type>
- or <type>timestamp with time zone</type> value, the months, days, and
- microseconds fields of the <type>interval</type> value are handled in turn.
- First, a nonzero months field advances or decrements the date of the
- timestamp by the indicated number of months, keeping the day of month the
- same unless it would be past the end of the new month, in which case the
- last day of that month is used. (For example, March 31 plus 1 month
- becomes April 30, but March 31 plus 2 months becomes May 31.)
- Then the days field advances or decrements the date of the timestamp by
- the indicated number of days. In both these steps the local time of day
- is kept the same. Finally, if there is a nonzero microseconds field, it
- is added or subtracted literally.
- When doing arithmetic on a <type>timestamp with time zone</type> value in
- a time zone that recognizes DST, this means that adding or subtracting
- (say) <literal>interval '1 day'</literal> does not necessarily have the
- same result as adding or subtracting <literal>interval '24
- hours'</literal>.
- For example, with the session time zone set
- to <literal>America/Denver</literal>:
-<screen>
-SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';
-<lineannotation>Result: </lineannotation><computeroutput>2005-04-03 12:00:00-06</computeroutput>
-SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';
-<lineannotation>Result: </lineannotation><computeroutput>2005-04-03 13:00:00-06</computeroutput>
-</screen>
- This happens because an hour was skipped due to a change in daylight saving
- time at <literal>2005-04-03 02:00:00</literal> in time zone
- <literal>America/Denver</literal>.
- </para>
-
- <para>
- Note there can be ambiguity in the <literal>months</literal> field returned by
- <function>age</function> because different months have different numbers of
- days. <productname>PostgreSQL</productname>'s approach uses the month from the
- earlier of the two dates when calculating partial months. For example,
- <literal>age('2004-06-01', '2004-04-30')</literal> uses April to yield
- <literal>1 mon 1 day</literal>, while using May would yield <literal>1 mon 2
- days</literal> because May has 31 days, while April has only 30.
- </para>
-
- <para>
- Subtraction of dates and timestamps can also be complex. One conceptually
- simple way to perform subtraction is to convert each value to a number
- of seconds using <literal>EXTRACT(EPOCH FROM ...)</literal>, then subtract the
- results; this produces the
- number of <emphasis>seconds</emphasis> between the two values. This will adjust
- for the number of days in each month, timezone changes, and daylight
- saving time adjustments. Subtraction of date or timestamp
- values with the <quote><literal>-</literal></quote> operator
- returns the number of days (24-hours) and hours/minutes/seconds
- between the values, making the same adjustments. The <function>age</function>
- function returns years, months, days, and hours/minutes/seconds,
- performing field-by-field subtraction and then adjusting for negative
- field values. The following queries illustrate the differences in these
- approaches. The sample results were produced with <literal>timezone
- = 'US/Eastern'</literal>; there is a daylight saving time change between the
- two dates used:
- </para>
-
-<screen>
-SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
- EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
-<lineannotation>Result: </lineannotation><computeroutput>10537200.000000</computeroutput>
-SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
- EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
- / 60 / 60 / 24;
-<lineannotation>Result: </lineannotation><computeroutput>121.9583333333333333</computeroutput>
-SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
-<lineannotation>Result: </lineannotation><computeroutput>121 days 23:00:00</computeroutput>
-SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
-<lineannotation>Result: </lineannotation><computeroutput>4 mons</computeroutput>
-</screen>
-
- <sect2 id="functions-datetime-extract">
- <title><function>EXTRACT</function>, <function>date_part</function></title>
-
- <indexterm>
- <primary>date_part</primary>
- </indexterm>
- <indexterm>
- <primary>extract</primary>
- </indexterm>
-
-<synopsis>
-EXTRACT(<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
-</synopsis>
-
- <para>
- The <function>extract</function> function retrieves subfields
- such as year or hour from date/time values.
- <replaceable>source</replaceable> must be a value expression of
- type <type>timestamp</type>, <type>date</type>, <type>time</type>,
- or <type>interval</type>. (Timestamps and times can be with or
- without time zone.)
- <replaceable>field</replaceable> is an identifier or
- string that selects what field to extract from the source value.
- Not all fields are valid for every input data type; for example, fields
- smaller than a day cannot be extracted from a <type>date</type>, while
- fields of a day or more cannot be extracted from a <type>time</type>.
- The <function>extract</function> function returns values of type
- <type>numeric</type>.
- </para>
-
- <para>
- The following are valid field names:
-
- <!-- alphabetical -->
- <variablelist>
- <varlistentry>
- <term><literal>century</literal></term>
- <listitem>
- <para>
- The century; for <type>interval</type> values, the year field
- divided by 100
- </para>
-
-<screen>
-SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
-<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
-SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>21</computeroutput>
-SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');
-<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
-SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');
-<lineannotation>Result: </lineannotation><computeroutput>-1</computeroutput>
-SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years');
-<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>day</literal></term>
- <listitem>
- <para>
- The day of the month (1–31); for <type>interval</type>
- values, the number of days
- </para>
-
-<screen>
-SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
-SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
-<lineannotation>Result: </lineannotation><computeroutput>40</computeroutput>
-</screen>
-
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>decade</literal></term>
- <listitem>
- <para>
- The year field divided by 10
- </para>
-
-<screen>
-SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>200</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>dow</literal></term>
- <listitem>
- <para>
- The day of the week as Sunday (<literal>0</literal>) to
- Saturday (<literal>6</literal>)
- </para>
-
-<screen>
-SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>5</computeroutput>
-</screen>
- <para>
- Note that <function>extract</function>'s day of the week numbering
- differs from that of the <function>to_char(...,
- 'D')</function> function.
- </para>
-
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>doy</literal></term>
- <listitem>
- <para>
- The day of the year (1–365/366)
- </para>
-
-<screen>
-SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>47</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>epoch</literal></term>
- <listitem>
- <para>
- For <type>timestamp with time zone</type> values, the
- 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
- 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>
-
-<screen>
-SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
-<lineannotation>Result: </lineannotation><computeroutput>982384720.120000</computeroutput>
-SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
-<lineannotation>Result: </lineannotation><computeroutput>982355920.120000</computeroutput>
-SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
-<lineannotation>Result: </lineannotation><computeroutput>442800.000000</computeroutput>
-</screen>
-
- <para>
- 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>
-
- <varlistentry>
- <term><literal>hour</literal></term>
- <listitem>
- <para>
- The hour field (0–23 in timestamps, unrestricted in
- intervals)
- </para>
-
-<screen>
-SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>isodow</literal></term>
- <listitem>
- <para>
- The day of the week as Monday (<literal>1</literal>) to
- Sunday (<literal>7</literal>)
- </para>
-
-<screen>
-SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
-</screen>
- <para>
- This is identical to <literal>dow</literal> except for Sunday. This
- matches the <acronym>ISO</acronym> 8601 day of the week numbering.
- </para>
-
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>isoyear</literal></term>
- <listitem>
- <para>
- The <acronym>ISO</acronym> 8601 week-numbering year that the date
- falls in
- </para>
-
-<screen>
-SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
-<lineannotation>Result: </lineannotation><computeroutput>2005</computeroutput>
-SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
-<lineannotation>Result: </lineannotation><computeroutput>2006</computeroutput>
-</screen>
-
- <para>
- Each <acronym>ISO</acronym> 8601 week-numbering year begins with the
- Monday of the week containing the 4th of January, so in early
- January or late December the <acronym>ISO</acronym> year may be
- different from the Gregorian year. See the <literal>week</literal>
- field for more information.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>julian</literal></term>
- <listitem>
- <para>
- The <firstterm>Julian Date</firstterm> corresponding to the
- date or timestamp. Timestamps
- that are not local midnight result in a fractional value. See
- <xref linkend="datetime-julian-dates"/> for more information.
- </para>
-
-<screen>
-SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
-<lineannotation>Result: </lineannotation><computeroutput>2453737</computeroutput>
-SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
-<lineannotation>Result: </lineannotation><computeroutput>2453737.50000000000000000000</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>microseconds</literal></term>
- <listitem>
- <para>
- The seconds field, including fractional parts, multiplied by 1
- 000 000; note that this includes full seconds
- </para>
-
-<screen>
-SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
-<lineannotation>Result: </lineannotation><computeroutput>28500000</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>millennium</literal></term>
- <listitem>
- <para>
- The millennium; for <type>interval</type> values, the year field
- divided by 1000
- </para>
-
-<screen>
-SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
-SELECT EXTRACT(MILLENNIUM FROM INTERVAL '2001 years');
-<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
-</screen>
-
- <para>
- Years in the 1900s are in the second millennium.
- The third millennium started January 1, 2001.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>milliseconds</literal></term>
- <listitem>
- <para>
- The seconds field, including fractional parts, multiplied by
- 1000. Note that this includes full seconds.
- </para>
-
-<screen>
-SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
-<lineannotation>Result: </lineannotation><computeroutput>28500.000</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>minute</literal></term>
- <listitem>
- <para>
- The minutes field (0–59)
- </para>
-
-<screen>
-SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>38</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>month</literal></term>
- <listitem>
- <para>
- The number of the month within the year (1–12);
- for <type>interval</type> values, the number of months modulo 12
- (0–11)
- </para>
-
-<screen>
-SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>2</computeroutput>
-SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
-<lineannotation>Result: </lineannotation><computeroutput>3</computeroutput>
-SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
-<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>quarter</literal></term>
- <listitem>
- <para>
- The quarter of the year (1–4) that the date is in
- </para>
-
-<screen>
-SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>second</literal></term>
- <listitem>
- <para>
- The seconds field, including any fractional seconds
- </para>
-
-<screen>
-SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>40.000000</computeroutput>
-SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
-<lineannotation>Result: </lineannotation><computeroutput>28.500000</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
- <varlistentry>
- <term><literal>timezone</literal></term>
- <listitem>
- <para>
- The time zone offset from UTC, measured in seconds. Positive values
- correspond to time zones east of UTC, negative values to
- zones west of UTC. (Technically,
- <productname>PostgreSQL</productname> does not use UTC because
- leap seconds are not handled.)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>timezone_hour</literal></term>
- <listitem>
- <para>
- The hour component of the time zone offset
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>timezone_minute</literal></term>
- <listitem>
- <para>
- The minute component of the time zone offset
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>week</literal></term>
- <listitem>
- <para>
- The number of the <acronym>ISO</acronym> 8601 week-numbering week of
- the year. By definition, ISO weeks start on Mondays and the first
- week of a year contains January 4 of that year. In other words, the
- first Thursday of a year is in week 1 of that year.
- </para>
- <para>
- In the ISO week-numbering system, it is possible for early-January
- dates to be part of the 52nd or 53rd week of the previous year, and for
- late-December dates to be part of the first week of the next year.
- For example, <literal>2005-01-01</literal> is part of the 53rd week of year
- 2004, and <literal>2006-01-01</literal> is part of the 52nd week of year
- 2005, while <literal>2012-12-31</literal> is part of the first week of 2013.
- It's recommended to use the <literal>isoyear</literal> field together with
- <literal>week</literal> to get consistent results.
- </para>
-
-<screen>
-SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>7</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>year</literal></term>
- <listitem>
- <para>
- The year field. Keep in mind there is no <literal>0 AD</literal>, so subtracting
- <literal>BC</literal> years from <literal>AD</literal> years should be done with care.
- </para>
-
-<screen>
-SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>2001</computeroutput>
-</screen>
- </listitem>
- </varlistentry>
-
- </variablelist>
- </para>
-
- <para>
- When processing an <type>interval</type> value,
- the <function>extract</function> function produces field values that
- match the interpretation used by the interval output function. This
- can produce surprising results if one starts with a non-normalized
- interval representation, for example:
-<screen>
-SELECT INTERVAL '80 minutes';
-<lineannotation>Result: </lineannotation><computeroutput>01:20:00</computeroutput>
-SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
-<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
-</screen>
- </para>
-
- <note>
- <para>
- When the input value is +/-Infinity, <function>extract</function> returns
- +/-Infinity for monotonically-increasing fields (<literal>epoch</literal>,
- <literal>julian</literal>, <literal>year</literal>, <literal>isoyear</literal>,
- <literal>decade</literal>, <literal>century</literal>, and <literal>millennium</literal>
- for <type>timestamp</type> inputs; <literal>epoch</literal>, <literal>hour</literal>,
- <literal>day</literal>, <literal>year</literal>, <literal>decade</literal>,
- <literal>century</literal>, and <literal>millennium</literal> for
- <type>interval</type> inputs).
- For other fields, NULL is returned. <productname>PostgreSQL</productname>
- versions before 9.6 returned zero for all cases of infinite input.
- </para>
- </note>
-
- <para>
- The <function>extract</function> function is primarily intended
- for computational processing. For formatting date/time values for
- display, see <xref linkend="functions-formatting"/>.
- </para>
-
- <para>
- The <function>date_part</function> function is modeled on the traditional
- <productname>Ingres</productname> equivalent to the
- <acronym>SQL</acronym>-standard function <function>extract</function>:
-<synopsis>
-date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
-</synopsis>
- Note that here the <replaceable>field</replaceable> parameter needs to
- be a string value, not a name. The valid field names for
- <function>date_part</function> are the same as for
- <function>extract</function>.
- For historical reasons, the <function>date_part</function> function
- returns values of type <type>double precision</type>. This can result in
- a loss of precision in certain uses. Using <function>extract</function>
- is recommended instead.
- </para>
-
-<screen>
-SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>16</computeroutput>
-SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
-<lineannotation>Result: </lineannotation><computeroutput>4</computeroutput>
-</screen>
-
- </sect2>
-
- <sect2 id="functions-datetime-trunc">
- <title><function>date_trunc</function></title>
-
- <indexterm>
- <primary>date_trunc</primary>
- </indexterm>
-
- <para>
- The function <function>date_trunc</function> is conceptually
- similar to the <function>trunc</function> function for numbers.
- </para>
-
- <para>
-<synopsis>
-date_trunc(<replaceable>field</replaceable>, <replaceable>source</replaceable> [, <replaceable>time_zone</replaceable> ])
-</synopsis>
- <replaceable>source</replaceable> is a value expression of type
- <type>timestamp</type>, <type>timestamp with time zone</type>,
- or <type>interval</type>.
- (Values of type <type>date</type> and
- <type>time</type> are cast automatically to <type>timestamp</type> or
- <type>interval</type>, respectively.)
- <replaceable>field</replaceable> selects to which precision to
- truncate the input value. The return value is likewise of type
- <type>timestamp</type>, <type>timestamp with time zone</type>,
- or <type>interval</type>,
- and it has all fields that are less significant than the
- selected one set to zero (or one, for day and month).
- </para>
-
- <para>
- Valid values for <replaceable>field</replaceable> are:
- <simplelist>
- <member><literal>microseconds</literal></member>
- <member><literal>milliseconds</literal></member>
- <member><literal>second</literal></member>
- <member><literal>minute</literal></member>
- <member><literal>hour</literal></member>
- <member><literal>day</literal></member>
- <member><literal>week</literal></member>
- <member><literal>month</literal></member>
- <member><literal>quarter</literal></member>
- <member><literal>year</literal></member>
- <member><literal>decade</literal></member>
- <member><literal>century</literal></member>
- <member><literal>millennium</literal></member>
- </simplelist>
- </para>
-
- <para>
- When the input value is of type <type>timestamp with time zone</type>,
- the truncation is performed with respect to a particular time zone;
- for example, truncation to <literal>day</literal> produces a value that
- is midnight in that zone. By default, truncation is done with respect
- to the current <xref linkend="guc-timezone"/> setting, but the
- optional <replaceable>time_zone</replaceable> argument can be provided
- to specify a different time zone. The time zone name can be specified
- in any of the ways described in <xref linkend="datatype-timezones"/>.
- </para>
-
- <para>
- A time zone cannot be specified when processing <type>timestamp without
- time zone</type> or <type>interval</type> inputs. These are always
- taken at face value.
- </para>
-
- <para>
- Examples (assuming the local time zone is <literal>America/New_York</literal>):
-<screen>
-SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:00:00</computeroutput>
-SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
-<lineannotation>Result: </lineannotation><computeroutput>2001-01-01 00:00:00</computeroutput>
-SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
-<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 00:00:00-05</computeroutput>
-SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
-<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 08:00:00-05</computeroutput>
-SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
-<lineannotation>Result: </lineannotation><computeroutput>3 days 02:00:00</computeroutput>
-</screen>
- </para>
- </sect2>
-
- <sect2 id="functions-datetime-bin">
- <title><function>date_bin</function></title>
-
- <indexterm>
- <primary>date_bin</primary>
- </indexterm>
-
- <para>
- The function <function>date_bin</function> <quote>bins</quote> the input
- timestamp into the specified interval (the <firstterm>stride</firstterm>)
- aligned with a specified origin.
- </para>
-
- <para>
-<synopsis>
-date_bin(<replaceable>stride</replaceable>, <replaceable>source</replaceable>, <replaceable>origin</replaceable>)
-</synopsis>
- <replaceable>source</replaceable> is a value expression of type
- <type>timestamp</type> or <type>timestamp with time zone</type>. (Values
- of type <type>date</type> are cast automatically to
- <type>timestamp</type>.) <replaceable>stride</replaceable> is a value
- expression of type <type>interval</type>. The return value is likewise
- of type <type>timestamp</type> or <type>timestamp with time zone</type>,
- and it marks the beginning of the bin into which the
- <replaceable>source</replaceable> is placed.
- </para>
-
- <para>
- Examples:
-<screen>
-SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
-<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:30:00</computeroutput>
-SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
-<lineannotation>Result: </lineannotation><computeroutput>2020-02-11 15:32:30</computeroutput>
-</screen>
- </para>
-
- <para>
- In the case of full units (1 minute, 1 hour, etc.), it gives the same result as
- the analogous <function>date_trunc</function> call, but the difference is
- that <function>date_bin</function> can truncate to an arbitrary interval.
- </para>
-
- <para>
- The <parameter>stride</parameter> interval must be greater than zero and
- cannot contain units of month or larger.
- </para>
- </sect2>
-
- <sect2 id="functions-datetime-zoneconvert">
- <title><literal>AT TIME ZONE and AT LOCAL</literal></title>
-
- <indexterm>
- <primary>time zone</primary>
- <secondary>conversion</secondary>
- </indexterm>
-
- <indexterm>
- <primary>AT TIME ZONE</primary>
- </indexterm>
-
- <indexterm>
- <primary>AT LOCAL</primary>
- </indexterm>
-
- <para>
- The <literal>AT TIME ZONE</literal> operator converts time
- stamp <emphasis>without</emphasis> time zone to/from
- time stamp <emphasis>with</emphasis> time zone, and
- <type>time with time zone</type> values to different time
- zones. <xref linkend="functions-datetime-zoneconvert-table"/> shows its
- variants.
- </para>
-
- <table id="functions-datetime-zoneconvert-table">
- <title><literal>AT TIME ZONE</literal> and <literal>AT LOCAL</literal> Variants</title>
- <tgroup cols="1">
- <thead>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- Operator
- </para>
- <para>
- Description
- </para>
- <para>
- Example(s)
- </para></entry>
- </row>
- </thead>
-
- <tbody>
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>timestamp without time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Converts given time stamp <emphasis>without</emphasis> time zone to
- time stamp <emphasis>with</emphasis> time zone, assuming the given
- value is in the named time zone.
- </para>
- <para>
- <literal>timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'</literal>
- <returnvalue>2001-02-17 03:38:40+00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>timestamp without time zone</type> <literal>AT LOCAL</literal>
- <returnvalue>timestamp with time zone</returnvalue>
- </para>
- <para>
- Converts given time stamp <emphasis>without</emphasis> time zone to
- time stamp <emphasis>with</emphasis> the session's
- <varname>TimeZone</varname> value as time zone.
- </para>
- <para>
- <literal>timestamp '2001-02-16 20:38:40' at local</literal>
- <returnvalue>2001-02-17 03:38:40+00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>timestamp with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
- <returnvalue>timestamp without time zone</returnvalue>
- </para>
- <para>
- Converts given time stamp <emphasis>with</emphasis> time zone to
- time stamp <emphasis>without</emphasis> time zone, as the time would
- appear in that zone.
- </para>
- <para>
- <literal>timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver'</literal>
- <returnvalue>2001-02-16 18:38:40</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>timestamp with time zone</type> <literal>AT LOCAL</literal>
- <returnvalue>timestamp without time zone</returnvalue>
- </para>
- <para>
- Converts given time stamp <emphasis>with</emphasis> time zone to
- time stamp <emphasis>without</emphasis> time zone, as the time would
- appear with the session's <varname>TimeZone</varname> value as time zone.
- </para>
- <para>
- <literal>timestamp with time zone '2001-02-16 20:38:40-05' at local</literal>
- <returnvalue>2001-02-16 18:38:40</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>time with time zone</type> <literal>AT TIME ZONE</literal> <replaceable>zone</replaceable>
- <returnvalue>time with time zone</returnvalue>
- </para>
- <para>
- Converts given time <emphasis>with</emphasis> time zone to a new time
- zone. Since no date is supplied, this uses the currently active UTC
- offset for the named destination zone.
- </para>
- <para>
- <literal>time with time zone '05:34:17-05' at time zone 'UTC'</literal>
- <returnvalue>10:34:17+00</returnvalue>
- </para></entry>
- </row>
-
- <row>
- <entry role="func_table_entry"><para role="func_signature">
- <type>time with time zone</type> <literal>AT LOCAL</literal>
- <returnvalue>time with time zone</returnvalue>
- </para>
- <para>
- Converts given time <emphasis>with</emphasis> time zone to a new time
- zone. Since no date is supplied, this uses the currently active UTC
- offset for the session's <varname>TimeZone</varname> value.
- </para>
- <para>
- Assuming the session's <varname>TimeZone</varname> is set to <literal>UTC</literal>:
- </para>
- <para>
- <literal>time with time zone '05:34:17-05' at local</literal>
- <returnvalue>10:34:17+00</returnvalue>
- </para></entry>
- </row>
- </tbody>
- </tgroup>
- </table>
-
- <para>
- In these expressions, the desired time zone <replaceable>zone</replaceable> can be
- specified either as a text value (e.g., <literal>'America/Los_Angeles'</literal>)
- or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
- In the text case, a time zone name can be specified in any of the ways
- described in <xref linkend="datatype-timezones"/>.
- The interval case is only useful for zones that have fixed offsets from
- UTC, so it is not very common in practice.
- </para>
-
- <para>
- The syntax <literal>AT LOCAL</literal> may be used as shorthand for
- <literal>AT TIME ZONE <replaceable>local</replaceable></literal>, where
- <replaceable>local</replaceable> is the session's
- <varname>TimeZone</varname> value.
- </para>
-
- <para>
- Examples (assuming the current <xref linkend="guc-timezone"/> setting
- is <literal>America/Los_Angeles</literal>):
-<screen>
-SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
-<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>
-SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
-<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
-SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
-<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 05:38:40</computeroutput>
-SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;
-<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 17:38:40</computeroutput>
-SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;
-<lineannotation>Result: </lineannotation><computeroutput>17:38:40</computeroutput>
-</screen>
- The first example adds a time zone to a value that lacks it, and
- displays the value using the current <varname>TimeZone</varname>
- setting. The second example shifts the time stamp with time zone value
- to the specified time zone, and returns the value without a time zone.
- This allows storage and display of values different from the current
- <varname>TimeZone</varname> setting. The third example converts
- Tokyo time to Chicago time. The fourth example shifts the time stamp
- with time zone value to the time zone currently specified by the
- <varname>TimeZone</varname> setting and returns the value without a
- time zone.
- </para>
-
- <para>
- The fifth example is a cautionary tale. Due to the fact that there is no
- date associated with the input value, the conversion is made using the
- current date of the session. Therefore, this static example may show a wrong
- result depending on the time of the year it is viewed because
- <literal>'America/Los_Angeles'</literal> observes Daylight Savings Time.
- </para>
-
- <para>
- The function <literal><function>timezone</function>(<replaceable>zone</replaceable>,
- <replaceable>timestamp</replaceable>)</literal> is equivalent to the SQL-conforming construct
- <literal><replaceable>timestamp</replaceable> AT TIME ZONE
- <replaceable>zone</replaceable></literal>.
- </para>
-
- <para>
- The function <literal><function>timezone</function>(<replaceable>zone</replaceable>,
- <replaceable>time</replaceable>)</literal> is equivalent to the SQL-conforming construct
- <literal><replaceable>time</replaceable> AT TIME ZONE
- <replaceable>zone</replaceable></literal>.
- </para>
-
- <para>
- The function <literal><function>timezone</function>(<replaceable>timestamp</replaceable>)</literal>
- is equivalent to the SQL-conforming construct <literal><replaceable>timestamp</replaceable>
- AT LOCAL</literal>.
- </para>
-
- <para>
- The function <literal><function>timezone</function>(<replaceable>time</replaceable>)</literal>
- is equivalent to the SQL-conforming construct <literal><replaceable>time</replaceable>
- AT LOCAL</literal>.
- </para>
- </sect2>
-
- <sect2 id="functions-datetime-current">
- <title>Current Date/Time</title>
-
- <indexterm>
- <primary>date</primary>
- <secondary>current</secondary>
- </indexterm>
-
- <indexterm>
- <primary>time</primary>
- <secondary>current</secondary>
- </indexterm>
-
- <para>
- <productname>PostgreSQL</productname> provides a number of functions
- that return values related to the current date and time. These
- SQL-standard functions all return values based on the start time of
- the current transaction:
-<synopsis>
-CURRENT_DATE
-CURRENT_TIME
-CURRENT_TIMESTAMP
-CURRENT_TIME(<replaceable>precision</replaceable>)
-CURRENT_TIMESTAMP(<replaceable>precision</replaceable>)
-LOCALTIME
-LOCALTIMESTAMP
-LOCALTIME(<replaceable>precision</replaceable>)
-LOCALTIMESTAMP(<replaceable>precision</replaceable>)
-</synopsis>
- </para>
-
- <para>
- <function>CURRENT_TIME</function> and
- <function>CURRENT_TIMESTAMP</function> deliver values with time zone;
- <function>LOCALTIME</function> and
- <function>LOCALTIMESTAMP</function> deliver values without time zone.
- </para>
-
- <para>
- <function>CURRENT_TIME</function>,
- <function>CURRENT_TIMESTAMP</function>,
- <function>LOCALTIME</function>, and
- <function>LOCALTIMESTAMP</function>
- can optionally take
- a precision parameter, which causes the result to be rounded
- to that many fractional digits in the seconds field. Without a precision parameter,
- the result is given to the full available precision.
- </para>
-
- <para>
- Some examples:
-<screen>
-SELECT CURRENT_TIME;
-<lineannotation>Result: </lineannotation><computeroutput>14:39:53.662522-05</computeroutput>
-SELECT CURRENT_DATE;
-<lineannotation>Result: </lineannotation><computeroutput>2019-12-23</computeroutput>
-SELECT CURRENT_TIMESTAMP;
-<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522-05</computeroutput>
-SELECT CURRENT_TIMESTAMP(2);
-<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.66-05</computeroutput>
-SELECT LOCALTIMESTAMP;
-<lineannotation>Result: </lineannotation><computeroutput>2019-12-23 14:39:53.662522</computeroutput>
-</screen>
- </para>
-
- <para>
- Since these functions return
- the start time of the current transaction, their values do not
- change during the transaction. This is considered a feature:
- the intent is to allow a single transaction to have a consistent
- notion of the <quote>current</quote> time, so that multiple
- modifications within the same transaction bear the same
- time stamp.
- </para>
-
- <note>
- <para>
- Other database systems might advance these values more
- frequently.
- </para>
- </note>
-
- <para>
- <productname>PostgreSQL</productname> also provides functions that
- return the start time of the current statement, as well as the actual
- current time at the instant the function is called. The complete list
- of non-SQL-standard time functions is:
-<synopsis>
-transaction_timestamp()
-statement_timestamp()
-clock_timestamp()
-timeofday()
-now()
-</synopsis>
- </para>
-
- <para>
- <function>transaction_timestamp()</function> is equivalent to
- <function>CURRENT_TIMESTAMP</function>, but is named to clearly reflect
- what it returns.
- <function>statement_timestamp()</function> returns the start time of the current
- statement (more specifically, the time of receipt of the latest command
- message from the client).
- <function>statement_timestamp()</function> and <function>transaction_timestamp()</function>
- return the same value during the first command of a transaction, but might
- differ during subsequent commands.
- <function>clock_timestamp()</function> returns the actual current time, and
- therefore its value changes even within a single SQL command.
- <function>timeofday()</function> is a historical
- <productname>PostgreSQL</productname> function. Like
- <function>clock_timestamp()</function>, it returns the actual current time,
- but as a formatted <type>text</type> string rather than a <type>timestamp
- with time zone</type> value.
- <function>now()</function> is a traditional <productname>PostgreSQL</productname>
- equivalent to <function>transaction_timestamp()</function>.
- </para>
-
- <para>
- All the date/time data types also accept the special literal value
- <literal>now</literal> to specify the current date and time (again,
- interpreted as the transaction start time). Thus,
- the following three all return the same result:
-<programlisting>
-SELECT CURRENT_TIMESTAMP;
-SELECT now();
-SELECT TIMESTAMP 'now'; -- but see tip below
-</programlisting>
- </para>
-
- <tip>
- <para>
- Do not use the third form when specifying a value to be evaluated later,
- for example in a <literal>DEFAULT</literal> clause for a table column.
- The system will convert <literal>now</literal>
- to a <type>timestamp</type> as soon as the constant is parsed, so that when
- the default value is needed,
- the time of the table creation would be used! The first two
- forms will not be evaluated until the default value is used,
- because they are function calls. Thus they will give the desired
- behavior of defaulting to the time of row insertion.
- (See also <xref linkend="datatype-datetime-special-values"/>.)
- </para>
- </tip>
- </sect2>
-
- <sect2 id="functions-datetime-delay">
- <title>Delaying Execution</title>
-
- <indexterm>
- <primary>pg_sleep</primary>
- </indexterm>
- <indexterm>
- <primary>pg_sleep_for</primary>
- </indexterm>
- <indexterm>
- <primary>pg_sleep_until</primary>
- </indexterm>
- <indexterm>
- <primary>sleep</primary>
- </indexterm>
- <indexterm>
- <primary>delay</primary>
- </indexterm>
-
- <para>
- The following functions are available to delay execution of the server
- process:
-<synopsis>
-pg_sleep ( <type>double precision</type> )
-pg_sleep_for ( <type>interval</type> )
-pg_sleep_until ( <type>timestamp with time zone</type> )
-</synopsis>
-
- <function>pg_sleep</function> makes the current session's process
- sleep until the given number of seconds have
- elapsed. Fractional-second delays can be specified.
- <function>pg_sleep_for</function> is a convenience function to
- allow the sleep time to be specified as an <type>interval</type>.
- <function>pg_sleep_until</function> is a convenience function for when
- a specific wake-up time is desired.
- For example:
-
-<programlisting>
-SELECT pg_sleep(1.5);
-SELECT pg_sleep_for('5 minutes');
-SELECT pg_sleep_until('tomorrow 03:00');
-</programlisting>
- </para>
-
- <note>
- <para>
- The effective resolution of the sleep interval is platform-specific;
- 0.01 seconds is a common value. The sleep delay will be at least as long
- as specified. It might be longer depending on factors such as server load.
- In particular, <function>pg_sleep_until</function> is not guaranteed to
- wake up exactly at the specified time, but it will not wake up any earlier.
- </para>
- </note>
-
- <warning>
- <para>
- Make sure that your session does not hold more locks than necessary
- when calling <function>pg_sleep</function> or its variants. Otherwise
- other sessions might have to wait for your sleeping process, slowing down
- the entire system.
- </para>
- </warning>
- </sect2>
-
- </sect1>
-
+&func-datetime;
<sect1 id="functions-enum">
<title>Enum Support Functions</title>
--
2.34.1