diff options
Diffstat (limited to 'doc/src/sgml/html/functions-datetime.html')
| -rw-r--r-- | doc/src/sgml/html/functions-datetime.html | 1316 |
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 => 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"><=</code> + <em class="replaceable"><code>time</code></em> <code class="literal"><</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 |