summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/functions-datetime.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/functions-datetime.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/functions-datetime.html')
-rw-r--r--doc/src/sgml/html/functions-datetime.html1316
1 files changed, 1316 insertions, 0 deletions
diff --git a/doc/src/sgml/html/functions-datetime.html b/doc/src/sgml/html/functions-datetime.html
new file mode 100644
index 0000000..a5b5eed
--- /dev/null
+++ b/doc/src/sgml/html/functions-datetime.html
@@ -0,0 +1,1316 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.9. Date/Time Functions and Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-formatting.html" title="9.8. Data Type Formatting Functions" /><link rel="next" href="functions-enum.html" title="9.10. Enum Support Functions" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.9. Date/Time Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-enum.html" title="9.10. Enum Support Functions">Next</a></td></tr></table><hr /></div><div class="sect1" id="FUNCTIONS-DATETIME"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.9. Date/Time Functions and Operators</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT">9.9.1. <code class="function">EXTRACT</code>, <code class="function">date_part</code></a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-TRUNC">9.9.2. <code class="function">date_trunc</code></a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-BIN">9.9.3. <code class="function">date_bin</code></a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT">9.9.4. <code class="literal">AT TIME ZONE</code></a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT">9.9.5. Current Date/Time</a></span></dt><dt><span class="sect2"><a href="functions-datetime.html#FUNCTIONS-DATETIME-DELAY">9.9.6. Delaying Execution</a></span></dt></dl></div><p>
+ <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-TABLE" title="Table 9.33. Date/Time Functions">Table 9.33</a> shows the available
+ functions for date/time value processing, with details appearing in
+ the following subsections. <a class="xref" href="functions-datetime.html#OPERATORS-DATETIME-TABLE" title="Table 9.32. Date/Time Operators">Table 9.32</a> illustrates the behaviors of
+ the basic arithmetic operators (<code class="literal">+</code>,
+ <code class="literal">*</code>, etc.). For formatting functions, refer to
+ <a class="xref" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Section 9.8</a>. You should be familiar with
+ the background information on date/time data types from <a class="xref" href="datatype-datetime.html" title="8.5. Date/Time Types">Section 8.5</a>.
+ </p><p>
+ In addition, the usual comparison operators shown in
+ <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a> 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 <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> 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 <code class="varname">TimeZone</code> zone when comparing it
+ to a timestamp.
+ </p><p>
+ All the functions and operators described below that take <code class="type">time</code> or <code class="type">timestamp</code>
+ inputs actually come in two variants: one that takes <code class="type">time with time zone</code> or <code class="type">timestamp
+ with time zone</code>, and one that takes <code class="type">time without time zone</code> or <code class="type">timestamp without time zone</code>.
+ For brevity, these variants are not shown separately. Also, the
+ <code class="literal">+</code> and <code class="literal">*</code> operators come in commutative pairs (for
+ example both <code class="type">date</code> <code class="literal">+</code> <code class="type">integer</code>
+ and <code class="type">integer</code> <code class="literal">+</code> <code class="type">date</code>); we show
+ only one of each such pair.
+ </p><div class="table" id="OPERATORS-DATETIME-TABLE"><p class="title"><strong>Table 9.32. Date/Time Operators</strong></p><div class="table-contents"><table class="table" summary="Date/Time Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
+ Operator
+ </p>
+ <p>
+ Description
+ </p>
+ <p>
+ Example(s)
+ </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">date</code> <code class="literal">+</code> <code class="type">integer</code>
+ → <code class="returnvalue">date</code>
+ </p>
+ <p>
+ Add a number of days to a date
+ </p>
+ <p>
+ <code class="literal">date '2001-09-28' + 7</code>
+ → <code class="returnvalue">2001-10-05</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">date</code> <code class="literal">+</code> <code class="type">interval</code>
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Add an interval to a date
+ </p>
+ <p>
+ <code class="literal">date '2001-09-28' + interval '1 hour'</code>
+ → <code class="returnvalue">2001-09-28 01:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">date</code> <code class="literal">+</code> <code class="type">time</code>
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Add a time-of-day to a date
+ </p>
+ <p>
+ <code class="literal">date '2001-09-28' + time '03:00'</code>
+ → <code class="returnvalue">2001-09-28 03:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">interval</code> <code class="literal">+</code> <code class="type">interval</code>
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Add intervals
+ </p>
+ <p>
+ <code class="literal">interval '1 day' + interval '1 hour'</code>
+ → <code class="returnvalue">1 day 01:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">timestamp</code> <code class="literal">+</code> <code class="type">interval</code>
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Add an interval to a timestamp
+ </p>
+ <p>
+ <code class="literal">timestamp '2001-09-28 01:00' + interval '23 hours'</code>
+ → <code class="returnvalue">2001-09-29 00:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">time</code> <code class="literal">+</code> <code class="type">interval</code>
+ → <code class="returnvalue">time</code>
+ </p>
+ <p>
+ Add an interval to a time
+ </p>
+ <p>
+ <code class="literal">time '01:00' + interval '3 hours'</code>
+ → <code class="returnvalue">04:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="literal">-</code> <code class="type">interval</code>
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Negate an interval
+ </p>
+ <p>
+ <code class="literal">- interval '23 hours'</code>
+ → <code class="returnvalue">-23:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">date</code> <code class="literal">-</code> <code class="type">date</code>
+ → <code class="returnvalue">integer</code>
+ </p>
+ <p>
+ Subtract dates, producing the number of days elapsed
+ </p>
+ <p>
+ <code class="literal">date '2001-10-01' - date '2001-09-28'</code>
+ → <code class="returnvalue">3</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">date</code> <code class="literal">-</code> <code class="type">integer</code>
+ → <code class="returnvalue">date</code>
+ </p>
+ <p>
+ Subtract a number of days from a date
+ </p>
+ <p>
+ <code class="literal">date '2001-10-01' - 7</code>
+ → <code class="returnvalue">2001-09-24</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">date</code> <code class="literal">-</code> <code class="type">interval</code>
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Subtract an interval from a date
+ </p>
+ <p>
+ <code class="literal">date '2001-09-28' - interval '1 hour'</code>
+ → <code class="returnvalue">2001-09-27 23:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">time</code> <code class="literal">-</code> <code class="type">time</code>
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Subtract times
+ </p>
+ <p>
+ <code class="literal">time '05:00' - time '03:00'</code>
+ → <code class="returnvalue">02:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">time</code> <code class="literal">-</code> <code class="type">interval</code>
+ → <code class="returnvalue">time</code>
+ </p>
+ <p>
+ Subtract an interval from a time
+ </p>
+ <p>
+ <code class="literal">time '05:00' - interval '2 hours'</code>
+ → <code class="returnvalue">03:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">timestamp</code> <code class="literal">-</code> <code class="type">interval</code>
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Subtract an interval from a timestamp
+ </p>
+ <p>
+ <code class="literal">timestamp '2001-09-28 23:00' - interval '23 hours'</code>
+ → <code class="returnvalue">2001-09-28 00:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">interval</code> <code class="literal">-</code> <code class="type">interval</code>
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Subtract intervals
+ </p>
+ <p>
+ <code class="literal">interval '1 day' - interval '1 hour'</code>
+ → <code class="returnvalue">1 day -01:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">timestamp</code> <code class="literal">-</code> <code class="type">timestamp</code>
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Subtract timestamps (converting 24-hour intervals into days,
+ similarly to <code class="function">justify_hours()</code>)
+ </p>
+ <p>
+ <code class="literal">timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'</code>
+ → <code class="returnvalue">63 days 15:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">interval</code> <code class="literal">*</code> <code class="type">double precision</code>
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Multiply an interval by a scalar
+ </p>
+ <p>
+ <code class="literal">interval '1 second' * 900</code>
+ → <code class="returnvalue">00:15:00</code>
+ </p>
+ <p>
+ <code class="literal">interval '1 day' * 21</code>
+ → <code class="returnvalue">21 days</code>
+ </p>
+ <p>
+ <code class="literal">interval '1 hour' * 3.5</code>
+ → <code class="returnvalue">03:30:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">interval</code> <code class="literal">/</code> <code class="type">double precision</code>
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Divide an interval by a scalar
+ </p>
+ <p>
+ <code class="literal">interval '1 hour' / 1.5</code>
+ → <code class="returnvalue">00:40:00</code>
+ </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="FUNCTIONS-DATETIME-TABLE"><p class="title"><strong>Table 9.33. Date/Time Functions</strong></p><div class="table-contents"><table class="table" summary="Date/Time Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
+ Function
+ </p>
+ <p>
+ Description
+ </p>
+ <p>
+ Example(s)
+ </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.1.1.1.1" class="indexterm"></a>
+ <code class="function">age</code> ( <code class="type">timestamp</code>, <code class="type">timestamp</code> )
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Subtract arguments, producing a <span class="quote">“<span class="quote">symbolic</span>”</span> result that
+ uses years and months, rather than just days
+ </p>
+ <p>
+ <code class="literal">age(timestamp '2001-04-10', timestamp '1957-06-13')</code>
+ → <code class="returnvalue">43 years 9 mons 27 days</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">age</code> ( <code class="type">timestamp</code> )
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Subtract argument from <code class="function">current_date</code> (at midnight)
+ </p>
+ <p>
+ <code class="literal">age(timestamp '1957-06-13')</code>
+ → <code class="returnvalue">62 years 6 mons 10 days</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.3.1.1.1" class="indexterm"></a>
+ <code class="function">clock_timestamp</code> ( )
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Current date and time (changes during statement execution);
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">clock_timestamp()</code>
+ → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.4.1.1.1" class="indexterm"></a>
+ <code class="function">current_date</code>
+ → <code class="returnvalue">date</code>
+ </p>
+ <p>
+ Current date; see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">current_date</code>
+ → <code class="returnvalue">2019-12-23</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.5.1.1.1" class="indexterm"></a>
+ <code class="function">current_time</code>
+ → <code class="returnvalue">time with time zone</code>
+ </p>
+ <p>
+ Current time of day; see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">current_time</code>
+ → <code class="returnvalue">14:39:53.662522-05</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">current_time</code> ( <code class="type">integer</code> )
+ → <code class="returnvalue">time with time zone</code>
+ </p>
+ <p>
+ Current time of day, with limited precision;
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">current_time(2)</code>
+ → <code class="returnvalue">14:39:53.66-05</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.7.1.1.1" class="indexterm"></a>
+ <code class="function">current_timestamp</code>
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Current date and time (start of current transaction);
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">current_timestamp</code>
+ → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">current_timestamp</code> ( <code class="type">integer</code> )
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Current date and time (start of current transaction), with limited precision;
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">current_timestamp(0)</code>
+ → <code class="returnvalue">2019-12-23 14:39:53-05</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">date_bin</code> ( <code class="type">interval</code>, <code class="type">timestamp</code>, <code class="type">timestamp</code> )
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Bin input into specified interval aligned with specified origin; see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-BIN" title="9.9.3. date_bin">Section 9.9.3</a>
+ </p>
+ <p>
+ <code class="literal">date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</code>
+ → <code class="returnvalue">2001-02-16 20:35:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.10.1.1.1" class="indexterm"></a>
+ <code class="function">date_part</code> ( <code class="type">text</code>, <code class="type">timestamp</code> )
+ → <code class="returnvalue">double precision</code>
+ </p>
+ <p>
+ Get timestamp subfield (equivalent to <code class="function">extract</code>);
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" title="9.9.1. EXTRACT, date_part">Section 9.9.1</a>
+ </p>
+ <p>
+ <code class="literal">date_part('hour', timestamp '2001-02-16 20:38:40')</code>
+ → <code class="returnvalue">20</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">date_part</code> ( <code class="type">text</code>, <code class="type">interval</code> )
+ → <code class="returnvalue">double precision</code>
+ </p>
+ <p>
+ Get interval subfield (equivalent to <code class="function">extract</code>);
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" title="9.9.1. EXTRACT, date_part">Section 9.9.1</a>
+ </p>
+ <p>
+ <code class="literal">date_part('month', interval '2 years 3 months')</code>
+ → <code class="returnvalue">3</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.12.1.1.1" class="indexterm"></a>
+ <code class="function">date_trunc</code> ( <code class="type">text</code>, <code class="type">timestamp</code> )
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Truncate to specified precision; see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-TRUNC" title="9.9.2. date_trunc">Section 9.9.2</a>
+ </p>
+ <p>
+ <code class="literal">date_trunc('hour', timestamp '2001-02-16 20:38:40')</code>
+ → <code class="returnvalue">2001-02-16 20:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">date_trunc</code> ( <code class="type">text</code>, <code class="type">timestamp with time zone</code>, <code class="type">text</code> )
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Truncate to specified precision in the specified time zone; see
+ <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-TRUNC" title="9.9.2. date_trunc">Section 9.9.2</a>
+ </p>
+ <p>
+ <code class="literal">date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')</code>
+ → <code class="returnvalue">2001-02-16 13:00:00+00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">date_trunc</code> ( <code class="type">text</code>, <code class="type">interval</code> )
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Truncate to specified precision; see
+ <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-TRUNC" title="9.9.2. date_trunc">Section 9.9.2</a>
+ </p>
+ <p>
+ <code class="literal">date_trunc('hour', interval '2 days 3 hours 40 minutes')</code>
+ → <code class="returnvalue">2 days 03:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.15.1.1.1" class="indexterm"></a>
+ <code class="function">extract</code> ( <em class="parameter"><code>field</code></em> <code class="literal">from</code> <code class="type">timestamp</code> )
+ → <code class="returnvalue">numeric</code>
+ </p>
+ <p>
+ Get timestamp subfield; see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" title="9.9.1. EXTRACT, date_part">Section 9.9.1</a>
+ </p>
+ <p>
+ <code class="literal">extract(hour from timestamp '2001-02-16 20:38:40')</code>
+ → <code class="returnvalue">20</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">extract</code> ( <em class="parameter"><code>field</code></em> <code class="literal">from</code> <code class="type">interval</code> )
+ → <code class="returnvalue">numeric</code>
+ </p>
+ <p>
+ Get interval subfield; see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" title="9.9.1. EXTRACT, date_part">Section 9.9.1</a>
+ </p>
+ <p>
+ <code class="literal">extract(month from interval '2 years 3 months')</code>
+ → <code class="returnvalue">3</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.17.1.1.1" class="indexterm"></a>
+ <code class="function">isfinite</code> ( <code class="type">date</code> )
+ → <code class="returnvalue">boolean</code>
+ </p>
+ <p>
+ Test for finite date (not +/-infinity)
+ </p>
+ <p>
+ <code class="literal">isfinite(date '2001-02-16')</code>
+ → <code class="returnvalue">true</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">isfinite</code> ( <code class="type">timestamp</code> )
+ → <code class="returnvalue">boolean</code>
+ </p>
+ <p>
+ Test for finite timestamp (not +/-infinity)
+ </p>
+ <p>
+ <code class="literal">isfinite(timestamp 'infinity')</code>
+ → <code class="returnvalue">false</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">isfinite</code> ( <code class="type">interval</code> )
+ → <code class="returnvalue">boolean</code>
+ </p>
+ <p>
+ Test for finite interval (currently always true)
+ </p>
+ <p>
+ <code class="literal">isfinite(interval '4 hours')</code>
+ → <code class="returnvalue">true</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.20.1.1.1" class="indexterm"></a>
+ <code class="function">justify_days</code> ( <code class="type">interval</code> )
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Adjust interval so 30-day time periods are represented as months
+ </p>
+ <p>
+ <code class="literal">justify_days(interval '35 days')</code>
+ → <code class="returnvalue">1 mon 5 days</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.21.1.1.1" class="indexterm"></a>
+ <code class="function">justify_hours</code> ( <code class="type">interval</code> )
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Adjust interval so 24-hour time periods are represented as days
+ </p>
+ <p>
+ <code class="literal">justify_hours(interval '27 hours')</code>
+ → <code class="returnvalue">1 day 03:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.22.1.1.1" class="indexterm"></a>
+ <code class="function">justify_interval</code> ( <code class="type">interval</code> )
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Adjust interval using <code class="function">justify_days</code>
+ and <code class="function">justify_hours</code>, with additional sign
+ adjustments
+ </p>
+ <p>
+ <code class="literal">justify_interval(interval '1 mon -1 hour')</code>
+ → <code class="returnvalue">29 days 23:00:00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.23.1.1.1" class="indexterm"></a>
+ <code class="function">localtime</code>
+ → <code class="returnvalue">time</code>
+ </p>
+ <p>
+ Current time of day;
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">localtime</code>
+ → <code class="returnvalue">14:39:53.662522</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">localtime</code> ( <code class="type">integer</code> )
+ → <code class="returnvalue">time</code>
+ </p>
+ <p>
+ Current time of day, with limited precision;
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">localtime(0)</code>
+ → <code class="returnvalue">14:39:53</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.25.1.1.1" class="indexterm"></a>
+ <code class="function">localtimestamp</code>
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Current date and time (start of current transaction);
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">localtimestamp</code>
+ → <code class="returnvalue">2019-12-23 14:39:53.662522</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="function">localtimestamp</code> ( <code class="type">integer</code> )
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Current date and time (start of current
+ transaction), with limited precision;
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">localtimestamp(2)</code>
+ → <code class="returnvalue">2019-12-23 14:39:53.66</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.27.1.1.1" class="indexterm"></a>
+ <code class="function">make_date</code> ( <em class="parameter"><code>year</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>month</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>day</code></em> <code class="type">int</code> )
+ → <code class="returnvalue">date</code>
+ </p>
+ <p>
+ Create date from year, month and day fields
+ (negative years signify BC)
+ </p>
+ <p>
+ <code class="literal">make_date(2013, 7, 15)</code>
+ → <code class="returnvalue">2013-07-15</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"><a id="id-1.5.8.15.6.2.2.28.1.1.1" class="indexterm"></a>
+ <code class="function">make_interval</code> ( [<span class="optional"> <em class="parameter"><code>years</code></em> <code class="type">int</code>
+ [<span class="optional">, <em class="parameter"><code>months</code></em> <code class="type">int</code>
+ [<span class="optional">, <em class="parameter"><code>weeks</code></em> <code class="type">int</code>
+ [<span class="optional">, <em class="parameter"><code>days</code></em> <code class="type">int</code>
+ [<span class="optional">, <em class="parameter"><code>hours</code></em> <code class="type">int</code>
+ [<span class="optional">, <em class="parameter"><code>mins</code></em> <code class="type">int</code>
+ [<span class="optional">, <em class="parameter"><code>secs</code></em> <code class="type">double precision</code>
+ </span>]</span>]</span>]</span>]</span>]</span>]</span>] )
+ → <code class="returnvalue">interval</code>
+ </p>
+ <p>
+ Create interval from years, months, weeks, days, hours, minutes and
+ seconds fields, each of which can default to zero
+ </p>
+ <p>
+ <code class="literal">make_interval(days =&gt; 10)</code>
+ → <code class="returnvalue">10 days</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.29.1.1.1" class="indexterm"></a>
+ <code class="function">make_time</code> ( <em class="parameter"><code>hour</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>min</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>sec</code></em> <code class="type">double precision</code> )
+ → <code class="returnvalue">time</code>
+ </p>
+ <p>
+ Create time from hour, minute and seconds fields
+ </p>
+ <p>
+ <code class="literal">make_time(8, 15, 23.5)</code>
+ → <code class="returnvalue">08:15:23.5</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.30.1.1.1" class="indexterm"></a>
+ <code class="function">make_timestamp</code> ( <em class="parameter"><code>year</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>month</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>day</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>hour</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>min</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>sec</code></em> <code class="type">double precision</code> )
+ → <code class="returnvalue">timestamp</code>
+ </p>
+ <p>
+ Create timestamp from year, month, day, hour, minute and seconds fields
+ (negative years signify BC)
+ </p>
+ <p>
+ <code class="literal">make_timestamp(2013, 7, 15, 8, 15, 23.5)</code>
+ → <code class="returnvalue">2013-07-15 08:15:23.5</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.31.1.1.1" class="indexterm"></a>
+ <code class="function">make_timestamptz</code> ( <em class="parameter"><code>year</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>month</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>day</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>hour</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>min</code></em> <code class="type">int</code>,
+ <em class="parameter"><code>sec</code></em> <code class="type">double precision</code>
+ [<span class="optional">, <em class="parameter"><code>timezone</code></em> <code class="type">text</code> </span>] )
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Create timestamp with time zone from year, month, day, hour, minute
+ and seconds fields (negative years signify BC).
+ If <em class="parameter"><code>timezone</code></em> is not
+ specified, the current time zone is used; the examples assume the
+ session time zone is <code class="literal">Europe/London</code>
+ </p>
+ <p>
+ <code class="literal">make_timestamptz(2013, 7, 15, 8, 15, 23.5)</code>
+ → <code class="returnvalue">2013-07-15 08:15:23.5+01</code>
+ </p>
+ <p>
+ <code class="literal">make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York')</code>
+ → <code class="returnvalue">2013-07-15 13:15:23.5+01</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.32.1.1.1" class="indexterm"></a>
+ <code class="function">now</code> ( )
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Current date and time (start of current transaction);
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">now()</code>
+ → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.33.1.1.1" class="indexterm"></a>
+ <code class="function">statement_timestamp</code> ( )
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Current date and time (start of current statement);
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">statement_timestamp()</code>
+ → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.34.1.1.1" class="indexterm"></a>
+ <code class="function">timeofday</code> ( )
+ → <code class="returnvalue">text</code>
+ </p>
+ <p>
+ Current date and time
+ (like <code class="function">clock_timestamp</code>, but as a <code class="type">text</code> string);
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">timeofday()</code>
+ → <code class="returnvalue">Mon Dec 23 14:39:53.662522 2019 EST</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.35.1.1.1" class="indexterm"></a>
+ <code class="function">transaction_timestamp</code> ( )
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Current date and time (start of current transaction);
+ see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>
+ </p>
+ <p>
+ <code class="literal">transaction_timestamp()</code>
+ → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <a id="id-1.5.8.15.6.2.2.36.1.1.1" class="indexterm"></a>
+ <code class="function">to_timestamp</code> ( <code class="type">double precision</code> )
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to
+ timestamp with time zone
+ </p>
+ <p>
+ <code class="literal">to_timestamp(1284352323)</code>
+ → <code class="returnvalue">2010-09-13 04:32:03+00</code>
+ </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
+ <a id="id-1.5.8.15.7.1" class="indexterm"></a>
+ In addition to these functions, the SQL <code class="literal">OVERLAPS</code> operator is
+ supported:
+</p><pre class="synopsis">
+(<em class="replaceable"><code>start1</code></em>, <em class="replaceable"><code>end1</code></em>) OVERLAPS (<em class="replaceable"><code>start2</code></em>, <em class="replaceable"><code>end2</code></em>)
+(<em class="replaceable"><code>start1</code></em>, <em class="replaceable"><code>length1</code></em>) OVERLAPS (<em class="replaceable"><code>start2</code></em>, <em class="replaceable"><code>length2</code></em>)
+</pre><p>
+ 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; <code class="literal">OVERLAPS</code> automatically takes the earlier value
+ of the pair as the start. Each time period is considered to
+ represent the half-open interval <em class="replaceable"><code>start</code></em> <code class="literal">&lt;=</code>
+ <em class="replaceable"><code>time</code></em> <code class="literal">&lt;</code> <em class="replaceable"><code>end</code></em>, unless
+ <em class="replaceable"><code>start</code></em> and <em class="replaceable"><code>end</code></em> 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.
+ </p><pre class="screen">
+SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
+ (DATE '2001-10-30', DATE '2002-10-30');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">true</code>
+SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS
+ (DATE '2001-10-30', DATE '2002-10-30');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">false</code>
+SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS
+ (DATE '2001-10-30', DATE '2001-10-31');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">false</code>
+SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS
+ (DATE '2001-10-30', DATE '2001-10-31');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">true</code>
+</pre><p>
+ When adding an <code class="type">interval</code> value to (or subtracting an
+ <code class="type">interval</code> value from) a <code class="type">timestamp with time zone</code>
+ value, the days component advances or decrements the date of the
+ <code class="type">timestamp with time zone</code> by the indicated number of days,
+ keeping the time of day the same.
+ Across daylight saving time changes (when the session time zone is set to a
+ time zone that recognizes DST), this means <code class="literal">interval '1 day'</code>
+ does not necessarily equal <code class="literal">interval '24 hours'</code>.
+ For example, with the session time zone set
+ to <code class="literal">America/Denver</code>:
+</p><pre class="screen">
+SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2005-04-03 12:00:00-06</code>
+SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2005-04-03 13:00:00-06</code>
+</pre><p>
+ This happens because an hour was skipped due to a change in daylight saving
+ time at <code class="literal">2005-04-03 02:00:00</code> in time zone
+ <code class="literal">America/Denver</code>.
+ </p><p>
+ Note there can be ambiguity in the <code class="literal">months</code> field returned by
+ <code class="function">age</code> because different months have different numbers of
+ days. <span class="productname">PostgreSQL</span>'s approach uses the month from the
+ earlier of the two dates when calculating partial months. For example,
+ <code class="literal">age('2004-06-01', '2004-04-30')</code> uses April to yield
+ <code class="literal">1 mon 1 day</code>, while using May would yield <code class="literal">1 mon 2
+ days</code> because May has 31 days, while April has only 30.
+ </p><p>
+ 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 <code class="literal">EXTRACT(EPOCH FROM ...)</code>, then subtract the
+ results; this produces the
+ number of <span class="emphasis"><em>seconds</em></span> 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 <span class="quote">“<span class="quote"><code class="literal">-</code></span>”</span> operator
+ returns the number of days (24-hours) and hours/minutes/seconds
+ between the values, making the same adjustments. The <code class="function">age</code>
+ 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 <code class="literal">timezone
+ = 'US/Eastern'</code>; there is a daylight saving time change between the
+ two dates used:
+ </p><pre class="screen">
+SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
+ EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">10537200.000000</code>
+SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
+ EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
+ / 60 / 60 / 24;
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">121.9583333333333333</code>
+SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">121 days 23:00:00</code>
+SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">4 mons</code>
+</pre><div class="sect2" id="FUNCTIONS-DATETIME-EXTRACT"><div class="titlepage"><div><div><h3 class="title">9.9.1. <code class="function">EXTRACT</code>, <code class="function">date_part</code></h3></div></div></div><a id="id-1.5.8.15.13.2" class="indexterm"></a><a id="id-1.5.8.15.13.3" class="indexterm"></a><pre class="synopsis">
+EXTRACT(<em class="replaceable"><code>field</code></em> FROM <em class="replaceable"><code>source</code></em>)
+</pre><p>
+ The <code class="function">extract</code> function retrieves subfields
+ such as year or hour from date/time values.
+ <em class="replaceable"><code>source</code></em> must be a value expression of
+ type <code class="type">timestamp</code>, <code class="type">time</code>, or <code class="type">interval</code>.
+ (Expressions of type <code class="type">date</code> are
+ cast to <code class="type">timestamp</code> and can therefore be used as
+ well.) <em class="replaceable"><code>field</code></em> is an identifier or
+ string that selects what field to extract from the source value.
+ The <code class="function">extract</code> function returns values of type
+ <code class="type">numeric</code>.
+ The following are valid field names:
+
+
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">century</code></span></dt><dd><p>
+ The century
+ </p><pre class="screen">
+SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">20</code>
+SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">21</code>
+</pre><p>
+ The first century starts at 0001-01-01 00:00:00 AD, although
+ they did not know it at the time. This definition applies to all
+ Gregorian calendar countries. There is no century number 0,
+ you go from -1 century to 1 century.
+
+ If you disagree with this, please write your complaint to:
+ Pope, Cathedral Saint-Peter of Roma, Vatican.
+ </p></dd><dt><span class="term"><code class="literal">day</code></span></dt><dd><p>
+ For <code class="type">timestamp</code> values, the day (of the month) field
+ (1–31) ; for <code class="type">interval</code> values, the number of days
+ </p><pre class="screen">
+SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">16</code>
+
+SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">40</code>
+</pre></dd><dt><span class="term"><code class="literal">decade</code></span></dt><dd><p>
+ The year field divided by 10
+ </p><pre class="screen">
+SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">200</code>
+</pre></dd><dt><span class="term"><code class="literal">dow</code></span></dt><dd><p>
+ The day of the week as Sunday (<code class="literal">0</code>) to
+ Saturday (<code class="literal">6</code>)
+ </p><pre class="screen">
+SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">5</code>
+</pre><p>
+ Note that <code class="function">extract</code>'s day of the week numbering
+ differs from that of the <code class="function">to_char(...,
+ 'D')</code> function.
+ </p></dd><dt><span class="term"><code class="literal">doy</code></span></dt><dd><p>
+ The day of the year (1–365/366)
+ </p><pre class="screen">
+SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">47</code>
+</pre></dd><dt><span class="term"><code class="literal">epoch</code></span></dt><dd><p>
+ For <code class="type">timestamp with time zone</code> values, the
+ number of seconds since 1970-01-01 00:00:00 UTC (negative for
+ timestamps before that);
+ for <code class="type">date</code> and <code class="type">timestamp</code> values, the
+ nominal number of seconds since 1970-01-01 00:00:00,
+ without regard to timezone or daylight-savings rules;
+ for <code class="type">interval</code> values, the total number
+ of seconds in the interval
+ </p><pre class="screen">
+SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">982384720.120000</code>
+
+SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">982355920.120000</code>
+
+SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">442800.000000</code>
+</pre><p>
+ You can convert an epoch value back to a <code class="type">timestamp with time zone</code>
+ with <code class="function">to_timestamp</code>:
+ </p><pre class="screen">
+SELECT to_timestamp(982384720.12);
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-17 04:38:40.12+00</code>
+</pre><p>
+ Beware that applying <code class="function">to_timestamp</code> to an epoch
+ extracted from a <code class="type">date</code> or <code class="type">timestamp</code> 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.
+ </p></dd><dt><span class="term"><code class="literal">hour</code></span></dt><dd><p>
+ The hour field (0–23)
+ </p><pre class="screen">
+SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">20</code>
+</pre></dd><dt><span class="term"><code class="literal">isodow</code></span></dt><dd><p>
+ The day of the week as Monday (<code class="literal">1</code>) to
+ Sunday (<code class="literal">7</code>)
+ </p><pre class="screen">
+SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">7</code>
+</pre><p>
+ This is identical to <code class="literal">dow</code> except for Sunday. This
+ matches the <acronym class="acronym">ISO</acronym> 8601 day of the week numbering.
+ </p></dd><dt><span class="term"><code class="literal">isoyear</code></span></dt><dd><p>
+ The <acronym class="acronym">ISO</acronym> 8601 week-numbering year that the date
+ falls in (not applicable to intervals)
+ </p><pre class="screen">
+SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2005</code>
+SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2006</code>
+</pre><p>
+ Each <acronym class="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 class="acronym">ISO</acronym> year may be
+ different from the Gregorian year. See the <code class="literal">week</code>
+ field for more information.
+ </p><p>
+ This field is not available in PostgreSQL releases prior to 8.3.
+ </p></dd><dt><span class="term"><code class="literal">julian</code></span></dt><dd><p>
+ The <em class="firstterm">Julian Date</em> corresponding to the
+ date or timestamp (not applicable to intervals). Timestamps
+ that are not local midnight result in a fractional value. See
+ <a class="xref" href="datetime-julian-dates.html" title="B.7. Julian Dates">Section B.7</a> for more information.
+ </p><pre class="screen">
+SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2453737</code>
+SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2453737.50000000000000000000</code>
+</pre></dd><dt><span class="term"><code class="literal">microseconds</code></span></dt><dd><p>
+ The seconds field, including fractional parts, multiplied by 1
+ 000 000; note that this includes full seconds
+ </p><pre class="screen">
+SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">28500000</code>
+</pre></dd><dt><span class="term"><code class="literal">millennium</code></span></dt><dd><p>
+ The millennium
+ </p><pre class="screen">
+SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">3</code>
+</pre><p>
+ Years in the 1900s are in the second millennium.
+ The third millennium started January 1, 2001.
+ </p></dd><dt><span class="term"><code class="literal">milliseconds</code></span></dt><dd><p>
+ The seconds field, including fractional parts, multiplied by
+ 1000. Note that this includes full seconds.
+ </p><pre class="screen">
+SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">28500.000</code>
+</pre></dd><dt><span class="term"><code class="literal">minute</code></span></dt><dd><p>
+ The minutes field (0–59)
+ </p><pre class="screen">
+SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">38</code>
+</pre></dd><dt><span class="term"><code class="literal">month</code></span></dt><dd><p>
+ For <code class="type">timestamp</code> values, the number of the month
+ within the year (1–12) ; for <code class="type">interval</code> values,
+ the number of months, modulo 12 (0–11)
+ </p><pre class="screen">
+SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2</code>
+
+SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">3</code>
+
+SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">1</code>
+</pre></dd><dt><span class="term"><code class="literal">quarter</code></span></dt><dd><p>
+ The quarter of the year (1–4) that the date is in
+ </p><pre class="screen">
+SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">1</code>
+</pre></dd><dt><span class="term"><code class="literal">second</code></span></dt><dd><p>
+ The seconds field, including any fractional seconds
+ </p><pre class="screen">
+SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">40.000000</code>
+
+SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">28.500000</code>
+</pre></dd><dt><span class="term"><code class="literal">timezone</code></span></dt><dd><p>
+ 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,
+ <span class="productname">PostgreSQL</span> does not use UTC because
+ leap seconds are not handled.)
+ </p></dd><dt><span class="term"><code class="literal">timezone_hour</code></span></dt><dd><p>
+ The hour component of the time zone offset
+ </p></dd><dt><span class="term"><code class="literal">timezone_minute</code></span></dt><dd><p>
+ The minute component of the time zone offset
+ </p></dd><dt><span class="term"><code class="literal">week</code></span></dt><dd><p>
+ The number of the <acronym class="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.
+ </p><p>
+ 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, <code class="literal">2005-01-01</code> is part of the 53rd week of year
+ 2004, and <code class="literal">2006-01-01</code> is part of the 52nd week of year
+ 2005, while <code class="literal">2012-12-31</code> is part of the first week of 2013.
+ It's recommended to use the <code class="literal">isoyear</code> field together with
+ <code class="literal">week</code> to get consistent results.
+ </p><pre class="screen">
+SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">7</code>
+</pre></dd><dt><span class="term"><code class="literal">year</code></span></dt><dd><p>
+ The year field. Keep in mind there is no <code class="literal">0 AD</code>, so subtracting
+ <code class="literal">BC</code> years from <code class="literal">AD</code> years should be done with care.
+ </p><pre class="screen">
+SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001</code>
+</pre></dd></dl></div><p>
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ When the input value is +/-Infinity, <code class="function">extract</code> returns
+ +/-Infinity for monotonically-increasing fields (<code class="literal">epoch</code>,
+ <code class="literal">julian</code>, <code class="literal">year</code>, <code class="literal">isoyear</code>,
+ <code class="literal">decade</code>, <code class="literal">century</code>, and <code class="literal">millennium</code>).
+ For other fields, NULL is returned. <span class="productname">PostgreSQL</span>
+ versions before 9.6 returned zero for all cases of infinite input.
+ </p></div><p>
+ The <code class="function">extract</code> function is primarily intended
+ for computational processing. For formatting date/time values for
+ display, see <a class="xref" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Section 9.8</a>.
+ </p><p>
+ The <code class="function">date_part</code> function is modeled on the traditional
+ <span class="productname">Ingres</span> equivalent to the
+ <acronym class="acronym">SQL</acronym>-standard function <code class="function">extract</code>:
+</p><pre class="synopsis">
+date_part('<em class="replaceable"><code>field</code></em>', <em class="replaceable"><code>source</code></em>)
+</pre><p>
+ Note that here the <em class="replaceable"><code>field</code></em> parameter needs to
+ be a string value, not a name. The valid field names for
+ <code class="function">date_part</code> are the same as for
+ <code class="function">extract</code>.
+ For historical reasons, the <code class="function">date_part</code> function
+ returns values of type <code class="type">double precision</code>. This can result in
+ a loss of precision in certain uses. Using <code class="function">extract</code>
+ is recommended instead.
+ </p><pre class="screen">
+SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">16</code>
+
+SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">4</code>
+</pre></div><div class="sect2" id="FUNCTIONS-DATETIME-TRUNC"><div class="titlepage"><div><div><h3 class="title">9.9.2. <code class="function">date_trunc</code></h3></div></div></div><a id="id-1.5.8.15.14.2" class="indexterm"></a><p>
+ The function <code class="function">date_trunc</code> is conceptually
+ similar to the <code class="function">trunc</code> function for numbers.
+ </p><p>
+</p><pre class="synopsis">
+date_trunc(<em class="replaceable"><code>field</code></em>, <em class="replaceable"><code>source</code></em> [, <em class="replaceable"><code>time_zone</code></em> ])
+</pre><p>
+ <em class="replaceable"><code>source</code></em> is a value expression of type
+ <code class="type">timestamp</code>, <code class="type">timestamp with time zone</code>,
+ or <code class="type">interval</code>.
+ (Values of type <code class="type">date</code> and
+ <code class="type">time</code> are cast automatically to <code class="type">timestamp</code> or
+ <code class="type">interval</code>, respectively.)
+ <em class="replaceable"><code>field</code></em> selects to which precision to
+ truncate the input value. The return value is likewise of type
+ <code class="type">timestamp</code>, <code class="type">timestamp with time zone</code>,
+ or <code class="type">interval</code>,
+ and it has all fields that are less significant than the
+ selected one set to zero (or one, for day and month).
+ </p><p>
+ Valid values for <em class="replaceable"><code>field</code></em> are:
+ </p><table border="0" summary="Simple list" class="simplelist"><tr><td><code class="literal">microseconds</code></td></tr><tr><td><code class="literal">milliseconds</code></td></tr><tr><td><code class="literal">second</code></td></tr><tr><td><code class="literal">minute</code></td></tr><tr><td><code class="literal">hour</code></td></tr><tr><td><code class="literal">day</code></td></tr><tr><td><code class="literal">week</code></td></tr><tr><td><code class="literal">month</code></td></tr><tr><td><code class="literal">quarter</code></td></tr><tr><td><code class="literal">year</code></td></tr><tr><td><code class="literal">decade</code></td></tr><tr><td><code class="literal">century</code></td></tr><tr><td><code class="literal">millennium</code></td></tr></table><p>
+ </p><p>
+ When the input value is of type <code class="type">timestamp with time zone</code>,
+ the truncation is performed with respect to a particular time zone;
+ for example, truncation to <code class="literal">day</code> produces a value that
+ is midnight in that zone. By default, truncation is done with respect
+ to the current <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting, but the
+ optional <em class="replaceable"><code>time_zone</code></em> argument can be provided
+ to specify a different time zone. The time zone name can be specified
+ in any of the ways described in <a class="xref" href="datatype-datetime.html#DATATYPE-TIMEZONES" title="8.5.3. Time Zones">Section 8.5.3</a>.
+ </p><p>
+ A time zone cannot be specified when processing <code class="type">timestamp without
+ time zone</code> or <code class="type">interval</code> inputs. These are always
+ taken at face value.
+ </p><p>
+ Examples (assuming the local time zone is <code class="literal">America/New_York</code>):
+</p><pre class="screen">
+SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 20:00:00</code>
+
+SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-01-01 00:00:00</code>
+
+SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 00:00:00-05</code>
+
+SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 08:00:00-05</code>
+
+SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">3 days 02:00:00</code>
+</pre><p>
+ </p></div><div class="sect2" id="FUNCTIONS-DATETIME-BIN"><div class="titlepage"><div><div><h3 class="title">9.9.3. <code class="function">date_bin</code></h3></div></div></div><a id="id-1.5.8.15.15.2" class="indexterm"></a><p>
+ The function <code class="function">date_bin</code> <span class="quote">“<span class="quote">bins</span>”</span> the input
+ timestamp into the specified interval (the <em class="firstterm">stride</em>)
+ aligned with a specified origin.
+ </p><p>
+</p><pre class="synopsis">
+date_bin(<em class="replaceable"><code>stride</code></em>, <em class="replaceable"><code>source</code></em>, <em class="replaceable"><code>origin</code></em>)
+</pre><p>
+ <em class="replaceable"><code>source</code></em> is a value expression of type
+ <code class="type">timestamp</code> or <code class="type">timestamp with time zone</code>. (Values
+ of type <code class="type">date</code> are cast automatically to
+ <code class="type">timestamp</code>.) <em class="replaceable"><code>stride</code></em> is a value
+ expression of type <code class="type">interval</code>. The return value is likewise
+ of type <code class="type">timestamp</code> or <code class="type">timestamp with time zone</code>,
+ and it marks the beginning of the bin into which the
+ <em class="replaceable"><code>source</code></em> is placed.
+ </p><p>
+ Examples:
+</p><pre class="screen">
+SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2020-02-11 15:30:00</code>
+
+SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2020-02-11 15:32:30</code>
+</pre><p>
+ </p><p>
+ In the case of full units (1 minute, 1 hour, etc.), it gives the same result as
+ the analogous <code class="function">date_trunc</code> call, but the difference is
+ that <code class="function">date_bin</code> can truncate to an arbitrary interval.
+ </p><p>
+ The <em class="parameter"><code>stride</code></em> interval must be greater than zero and
+ cannot contain units of month or larger.
+ </p></div><div class="sect2" id="FUNCTIONS-DATETIME-ZONECONVERT"><div class="titlepage"><div><div><h3 class="title">9.9.4. <code class="literal">AT TIME ZONE</code></h3></div></div></div><a id="id-1.5.8.15.16.2" class="indexterm"></a><a id="id-1.5.8.15.16.3" class="indexterm"></a><p>
+ The <code class="literal">AT TIME ZONE</code> operator converts time
+ stamp <span class="emphasis"><em>without</em></span> time zone to/from
+ time stamp <span class="emphasis"><em>with</em></span> time zone, and
+ <code class="type">time with time zone</code> values to different time
+ zones. <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT-TABLE" title="Table 9.34. AT TIME ZONE Variants">Table 9.34</a> shows its
+ variants.
+ </p><div class="table" id="FUNCTIONS-DATETIME-ZONECONVERT-TABLE"><p class="title"><strong>Table 9.34. <code class="literal">AT TIME ZONE</code> Variants</strong></p><div class="table-contents"><table class="table" summary="AT TIME ZONE Variants" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature">
+ Operator
+ </p>
+ <p>
+ Description
+ </p>
+ <p>
+ Example(s)
+ </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">timestamp without time zone</code> <code class="literal">AT TIME ZONE</code> <em class="replaceable"><code>zone</code></em>
+ → <code class="returnvalue">timestamp with time zone</code>
+ </p>
+ <p>
+ Converts given time stamp <span class="emphasis"><em>without</em></span> time zone to
+ time stamp <span class="emphasis"><em>with</em></span> time zone, assuming the given
+ value is in the named time zone.
+ </p>
+ <p>
+ <code class="literal">timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'</code>
+ → <code class="returnvalue">2001-02-17 03:38:40+00</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">timestamp with time zone</code> <code class="literal">AT TIME ZONE</code> <em class="replaceable"><code>zone</code></em>
+ → <code class="returnvalue">timestamp without time zone</code>
+ </p>
+ <p>
+ Converts given time stamp <span class="emphasis"><em>with</em></span> time zone to
+ time stamp <span class="emphasis"><em>without</em></span> time zone, as the time would
+ appear in that zone.
+ </p>
+ <p>
+ <code class="literal">timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver'</code>
+ → <code class="returnvalue">2001-02-16 18:38:40</code>
+ </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">
+ <code class="type">time with time zone</code> <code class="literal">AT TIME ZONE</code> <em class="replaceable"><code>zone</code></em>
+ → <code class="returnvalue">time with time zone</code>
+ </p>
+ <p>
+ Converts given time <span class="emphasis"><em>with</em></span> time zone to a new time
+ zone. Since no date is supplied, this uses the currently active UTC
+ offset for the named destination zone.
+ </p>
+ <p>
+ <code class="literal">time with time zone '05:34:17-05' at time zone 'UTC'</code>
+ → <code class="returnvalue">10:34:17+00</code>
+ </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
+ In these expressions, the desired time zone <em class="replaceable"><code>zone</code></em> can be
+ specified either as a text value (e.g., <code class="literal">'America/Los_Angeles'</code>)
+ or as an interval (e.g., <code class="literal">INTERVAL '-08:00'</code>).
+ In the text case, a time zone name can be specified in any of the ways
+ described in <a class="xref" href="datatype-datetime.html#DATATYPE-TIMEZONES" title="8.5.3. Time Zones">Section 8.5.3</a>.
+ The interval case is only useful for zones that have fixed offsets from
+ UTC, so it is not very common in practice.
+ </p><p>
+ Examples (assuming the current <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting
+ is <code class="literal">America/Los_Angeles</code>):
+</p><pre class="screen">
+SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 19:38:40-08</code>
+
+SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 18:38:40</code>
+
+SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'America/Chicago';
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 05:38:40</code>
+</pre><p>
+ The first example adds a time zone to a value that lacks it, and
+ displays the value using the current <code class="varname">TimeZone</code>
+ 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
+ <code class="varname">TimeZone</code> setting. The third example converts
+ Tokyo time to Chicago time.
+ </p><p>
+ The function <code class="literal"><code class="function">timezone</code>(<em class="replaceable"><code>zone</code></em>,
+ <em class="replaceable"><code>timestamp</code></em>)</code> is equivalent to the SQL-conforming construct
+ <code class="literal"><em class="replaceable"><code>timestamp</code></em> AT TIME ZONE
+ <em class="replaceable"><code>zone</code></em></code>.
+ </p></div><div class="sect2" id="FUNCTIONS-DATETIME-CURRENT"><div class="titlepage"><div><div><h3 class="title">9.9.5. Current Date/Time</h3></div></div></div><a id="id-1.5.8.15.17.2" class="indexterm"></a><a id="id-1.5.8.15.17.3" class="indexterm"></a><p>
+ <span class="productname">PostgreSQL</span> 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:
+</p><pre class="synopsis">
+CURRENT_DATE
+CURRENT_TIME
+CURRENT_TIMESTAMP
+CURRENT_TIME(<em class="replaceable"><code>precision</code></em>)
+CURRENT_TIMESTAMP(<em class="replaceable"><code>precision</code></em>)
+LOCALTIME
+LOCALTIMESTAMP
+LOCALTIME(<em class="replaceable"><code>precision</code></em>)
+LOCALTIMESTAMP(<em class="replaceable"><code>precision</code></em>)
+</pre><p>
+ </p><p>
+ <code class="function">CURRENT_TIME</code> and
+ <code class="function">CURRENT_TIMESTAMP</code> deliver values with time zone;
+ <code class="function">LOCALTIME</code> and
+ <code class="function">LOCALTIMESTAMP</code> deliver values without time zone.
+ </p><p>
+ <code class="function">CURRENT_TIME</code>,
+ <code class="function">CURRENT_TIMESTAMP</code>,
+ <code class="function">LOCALTIME</code>, and
+ <code class="function">LOCALTIMESTAMP</code>
+ 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.
+ </p><p>
+ Some examples:
+</p><pre class="screen">
+SELECT CURRENT_TIME;
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">14:39:53.662522-05</code>
+
+SELECT CURRENT_DATE;
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2019-12-23</code>
+
+SELECT CURRENT_TIMESTAMP;
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2019-12-23 14:39:53.662522-05</code>
+
+SELECT CURRENT_TIMESTAMP(2);
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2019-12-23 14:39:53.66-05</code>
+
+SELECT LOCALTIMESTAMP;
+<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2019-12-23 14:39:53.662522</code>
+</pre><p>
+ </p><p>
+ 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 <span class="quote">“<span class="quote">current</span>”</span> time, so that multiple
+ modifications within the same transaction bear the same
+ time stamp.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ Other database systems might advance these values more
+ frequently.
+ </p></div><p>
+ <span class="productname">PostgreSQL</span> 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:
+</p><pre class="synopsis">
+transaction_timestamp()
+statement_timestamp()
+clock_timestamp()
+timeofday()
+now()
+</pre><p>
+ </p><p>
+ <code class="function">transaction_timestamp()</code> is equivalent to
+ <code class="function">CURRENT_TIMESTAMP</code>, but is named to clearly reflect
+ what it returns.
+ <code class="function">statement_timestamp()</code> returns the start time of the current
+ statement (more specifically, the time of receipt of the latest command
+ message from the client).
+ <code class="function">statement_timestamp()</code> and <code class="function">transaction_timestamp()</code>
+ return the same value during the first command of a transaction, but might
+ differ during subsequent commands.
+ <code class="function">clock_timestamp()</code> returns the actual current time, and
+ therefore its value changes even within a single SQL command.
+ <code class="function">timeofday()</code> is a historical
+ <span class="productname">PostgreSQL</span> function. Like
+ <code class="function">clock_timestamp()</code>, it returns the actual current time,
+ but as a formatted <code class="type">text</code> string rather than a <code class="type">timestamp
+ with time zone</code> value.
+ <code class="function">now()</code> is a traditional <span class="productname">PostgreSQL</span>
+ equivalent to <code class="function">transaction_timestamp()</code>.
+ </p><p>
+ All the date/time data types also accept the special literal value
+ <code class="literal">now</code> to specify the current date and time (again,
+ interpreted as the transaction start time). Thus,
+ the following three all return the same result:
+</p><pre class="programlisting">
+SELECT CURRENT_TIMESTAMP;
+SELECT now();
+SELECT TIMESTAMP 'now'; -- but see tip below
+</pre><p>
+ </p><div class="tip"><h3 class="title">Tip</h3><p>
+ Do not use the third form when specifying a value to be evaluated later,
+ for example in a <code class="literal">DEFAULT</code> clause for a table column.
+ The system will convert <code class="literal">now</code>
+ to a <code class="type">timestamp</code> 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 <a class="xref" href="datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-VALUES" title="8.5.1.4. Special Values">Section 8.5.1.4</a>.)
+ </p></div></div><div class="sect2" id="FUNCTIONS-DATETIME-DELAY"><div class="titlepage"><div><div><h3 class="title">9.9.6. Delaying Execution</h3></div></div></div><a id="id-1.5.8.15.18.2" class="indexterm"></a><a id="id-1.5.8.15.18.3" class="indexterm"></a><a id="id-1.5.8.15.18.4" class="indexterm"></a><a id="id-1.5.8.15.18.5" class="indexterm"></a><a id="id-1.5.8.15.18.6" class="indexterm"></a><p>
+ The following functions are available to delay execution of the server
+ process:
+</p><pre class="synopsis">
+pg_sleep ( <code class="type">double precision</code> )
+pg_sleep_for ( <code class="type">interval</code> )
+pg_sleep_until ( <code class="type">timestamp with time zone</code> )
+</pre><p>
+
+ <code class="function">pg_sleep</code> makes the current session's process
+ sleep until the given number of seconds have
+ elapsed. Fractional-second delays can be specified.
+ <code class="function">pg_sleep_for</code> is a convenience function to
+ allow the sleep time to be specified as an <code class="type">interval</code>.
+ <code class="function">pg_sleep_until</code> is a convenience function for when
+ a specific wake-up time is desired.
+ For example:
+
+</p><pre class="programlisting">
+SELECT pg_sleep(1.5);
+SELECT pg_sleep_for('5 minutes');
+SELECT pg_sleep_until('tomorrow 03:00');
+</pre><p>
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ 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, <code class="function">pg_sleep_until</code> is not guaranteed to
+ wake up exactly at the specified time, but it will not wake up any earlier.
+ </p></div><div class="warning"><h3 class="title">Warning</h3><p>
+ Make sure that your session does not hold more locks than necessary
+ when calling <code class="function">pg_sleep</code> or its variants. Otherwise
+ other sessions might have to wait for your sleeping process, slowing down
+ the entire system.
+ </p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-enum.html" title="9.10. Enum Support Functions">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.8. Data Type Formatting Functions </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.10. Enum Support Functions</td></tr></table></div></body></html> \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-22 18:32:30 +0000