Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
functions-datetime.html1357 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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 16.3 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 <a href="#FUNCTIONS-DATETIME" class="id_link">#</a></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>3   <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-TABLE" title="Table 9.33. Date/Time Functions">Table 9.33</a> shows the available4   functions for date/time value processing, with details appearing in5   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 of6   the basic arithmetic operators (<code class="literal">+</code>,7   <code class="literal">*</code>, etc.).  For formatting functions, refer to8   <a class="xref" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Section 9.8</a>.  You should be familiar with9   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>.10  </p><p>11   In addition, the usual comparison operators shown in12   <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a> are available for the13   date/time types.  Dates and timestamps (with or without time zone) are14   all comparable, while times (with or without time zone) and intervals15   can only be compared to other values of the same data type.  When16   comparing a timestamp without time zone to a timestamp with time zone,17   the former value is assumed to be given in the time zone specified by18   the <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> configuration parameter, and is19   rotated to UTC for comparison to the latter value (which is already20   in UTC internally).  Similarly, a date value is assumed to represent21   midnight in the <code class="varname">TimeZone</code> zone when comparing it22   to a timestamp.23  </p><p>24   All the functions and operators described below that take <code class="type">time</code> or <code class="type">timestamp</code>25   inputs actually come in two variants: one that takes <code class="type">time with time zone</code> or <code class="type">timestamp26   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>.27   For brevity, these variants are not shown separately.  Also, the28   <code class="literal">+</code> and <code class="literal">*</code> operators come in commutative pairs (for29   example both <code class="type">date</code> <code class="literal">+</code> <code class="type">integer</code>30   and <code class="type">integer</code> <code class="literal">+</code> <code class="type">date</code>); we show31   only one of each such pair.32  </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">33         Operator34        </p>35        <p>36         Description37        </p>38        <p>39         Example(s)40        </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">41         <code class="type">date</code> <code class="literal">+</code> <code class="type">integer</code>42         → <code class="returnvalue">date</code>43        </p>44        <p>45         Add a number of days to a date46        </p>47        <p>48         <code class="literal">date '2001-09-28' + 7</code>49         → <code class="returnvalue">2001-10-05</code>50        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">51         <code class="type">date</code> <code class="literal">+</code> <code class="type">interval</code>52         → <code class="returnvalue">timestamp</code>53        </p>54        <p>55         Add an interval to a date56        </p>57        <p>58         <code class="literal">date '2001-09-28' + interval '1 hour'</code>59         → <code class="returnvalue">2001-09-28 01:00:00</code>60        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">61         <code class="type">date</code> <code class="literal">+</code> <code class="type">time</code>62         → <code class="returnvalue">timestamp</code>63        </p>64        <p>65         Add a time-of-day to a date66        </p>67        <p>68         <code class="literal">date '2001-09-28' + time '03:00'</code>69         → <code class="returnvalue">2001-09-28 03:00:00</code>70        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">71         <code class="type">interval</code> <code class="literal">+</code> <code class="type">interval</code>72         → <code class="returnvalue">interval</code>73        </p>74        <p>75         Add intervals76        </p>77        <p>78         <code class="literal">interval '1 day' + interval '1 hour'</code>79         → <code class="returnvalue">1 day 01:00:00</code>80        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">81         <code class="type">timestamp</code> <code class="literal">+</code> <code class="type">interval</code>82         → <code class="returnvalue">timestamp</code>83        </p>84        <p>85         Add an interval to a timestamp86        </p>87        <p>88         <code class="literal">timestamp '2001-09-28 01:00' + interval '23 hours'</code>89         → <code class="returnvalue">2001-09-29 00:00:00</code>90        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">91         <code class="type">time</code> <code class="literal">+</code> <code class="type">interval</code>92         → <code class="returnvalue">time</code>93        </p>94        <p>95         Add an interval to a time96        </p>97        <p>98         <code class="literal">time '01:00' + interval '3 hours'</code>99         → <code class="returnvalue">04:00:00</code>100        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">101         <code class="literal">-</code> <code class="type">interval</code>102         → <code class="returnvalue">interval</code>103        </p>104        <p>105         Negate an interval106        </p>107        <p>108         <code class="literal">- interval '23 hours'</code>109         → <code class="returnvalue">-23:00:00</code>110        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">111         <code class="type">date</code> <code class="literal">-</code> <code class="type">date</code>112         → <code class="returnvalue">integer</code>113        </p>114        <p>115         Subtract dates, producing the number of days elapsed116        </p>117        <p>118         <code class="literal">date '2001-10-01' - date '2001-09-28'</code>119         → <code class="returnvalue">3</code>120        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">121         <code class="type">date</code> <code class="literal">-</code> <code class="type">integer</code>122         → <code class="returnvalue">date</code>123        </p>124        <p>125         Subtract a number of days from a date126        </p>127        <p>128         <code class="literal">date '2001-10-01' - 7</code>129         → <code class="returnvalue">2001-09-24</code>130        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">131         <code class="type">date</code> <code class="literal">-</code> <code class="type">interval</code>132         → <code class="returnvalue">timestamp</code>133        </p>134        <p>135         Subtract an interval from a date136        </p>137        <p>138         <code class="literal">date '2001-09-28' - interval '1 hour'</code>139         → <code class="returnvalue">2001-09-27 23:00:00</code>140        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">141         <code class="type">time</code> <code class="literal">-</code> <code class="type">time</code>142         → <code class="returnvalue">interval</code>143        </p>144        <p>145         Subtract times146        </p>147        <p>148         <code class="literal">time '05:00' - time '03:00'</code>149         → <code class="returnvalue">02:00:00</code>150        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">151         <code class="type">time</code> <code class="literal">-</code> <code class="type">interval</code>152         → <code class="returnvalue">time</code>153        </p>154        <p>155         Subtract an interval from a time156        </p>157        <p>158         <code class="literal">time '05:00' - interval '2 hours'</code>159         → <code class="returnvalue">03:00:00</code>160        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">161         <code class="type">timestamp</code> <code class="literal">-</code> <code class="type">interval</code>162         → <code class="returnvalue">timestamp</code>163        </p>164        <p>165         Subtract an interval from a timestamp166        </p>167        <p>168         <code class="literal">timestamp '2001-09-28 23:00' - interval '23 hours'</code>169         → <code class="returnvalue">2001-09-28 00:00:00</code>170        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">171         <code class="type">interval</code> <code class="literal">-</code> <code class="type">interval</code>172         → <code class="returnvalue">interval</code>173        </p>174        <p>175         Subtract intervals176        </p>177        <p>178         <code class="literal">interval '1 day' - interval '1 hour'</code>179         → <code class="returnvalue">1 day -01:00:00</code>180        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">181         <code class="type">timestamp</code> <code class="literal">-</code> <code class="type">timestamp</code>182         → <code class="returnvalue">interval</code>183        </p>184        <p>185         Subtract timestamps (converting 24-hour intervals into days,186         similarly to <a class="link" href="functions-datetime.html#FUNCTION-JUSTIFY-HOURS"><code class="function">justify_hours()</code></a>)187        </p>188        <p>189         <code class="literal">timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12:00'</code>190         → <code class="returnvalue">63 days 15:00:00</code>191        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">192         <code class="type">interval</code> <code class="literal">*</code> <code class="type">double precision</code>193         → <code class="returnvalue">interval</code>194        </p>195        <p>196         Multiply an interval by a scalar197        </p>198        <p>199         <code class="literal">interval '1 second' * 900</code>200         → <code class="returnvalue">00:15:00</code>201        </p>202        <p>203         <code class="literal">interval '1 day' * 21</code>204         → <code class="returnvalue">21 days</code>205        </p>206        <p>207         <code class="literal">interval '1 hour' * 3.5</code>208         → <code class="returnvalue">03:30:00</code>209        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">210         <code class="type">interval</code> <code class="literal">/</code> <code class="type">double precision</code>211         → <code class="returnvalue">interval</code>212        </p>213        <p>214         Divide an interval by a scalar215        </p>216        <p>217         <code class="literal">interval '1 hour' / 1.5</code>218         → <code class="returnvalue">00:40:00</code>219        </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">220         Function221        </p>222        <p>223         Description224        </p>225        <p>226         Example(s)227        </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">228         <a id="id-1.5.8.15.6.2.2.1.1.1.1" class="indexterm"></a>229         <code class="function">age</code> ( <code class="type">timestamp</code>, <code class="type">timestamp</code> )230         → <code class="returnvalue">interval</code>231        </p>232        <p>233         Subtract arguments, producing a <span class="quote">“<span class="quote">symbolic</span>”</span> result that234         uses years and months, rather than just days235        </p>236        <p>237         <code class="literal">age(timestamp '2001-04-10', timestamp '1957-06-13')</code>238         → <code class="returnvalue">43 years 9 mons 27 days</code>239        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">240         <code class="function">age</code> ( <code class="type">timestamp</code> )241         → <code class="returnvalue">interval</code>242        </p>243        <p>244         Subtract argument from <code class="function">current_date</code> (at midnight)245        </p>246        <p>247         <code class="literal">age(timestamp '1957-06-13')</code>248         → <code class="returnvalue">62 years 6 mons 10 days</code>249        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">250         <a id="id-1.5.8.15.6.2.2.3.1.1.1" class="indexterm"></a>251         <code class="function">clock_timestamp</code> ( )252         → <code class="returnvalue">timestamp with time zone</code>253        </p>254        <p>255         Current date and time (changes during statement execution);256         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>257        </p>258        <p>259         <code class="literal">clock_timestamp()</code>260         → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>261        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">262         <a id="id-1.5.8.15.6.2.2.4.1.1.1" class="indexterm"></a>263         <code class="function">current_date</code>264         → <code class="returnvalue">date</code>265        </p>266        <p>267         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>268        </p>269        <p>270         <code class="literal">current_date</code>271         → <code class="returnvalue">2019-12-23</code>272        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">273         <a id="id-1.5.8.15.6.2.2.5.1.1.1" class="indexterm"></a>274         <code class="function">current_time</code>275         → <code class="returnvalue">time with time zone</code>276        </p>277        <p>278         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>279        </p>280        <p>281         <code class="literal">current_time</code>282         → <code class="returnvalue">14:39:53.662522-05</code>283        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">284         <code class="function">current_time</code> ( <code class="type">integer</code> )285         → <code class="returnvalue">time with time zone</code>286        </p>287        <p>288         Current time of day, with limited precision;289         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>290        </p>291        <p>292         <code class="literal">current_time(2)</code>293         → <code class="returnvalue">14:39:53.66-05</code>294        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">295         <a id="id-1.5.8.15.6.2.2.7.1.1.1" class="indexterm"></a>296         <code class="function">current_timestamp</code>297         → <code class="returnvalue">timestamp with time zone</code>298        </p>299        <p>300         Current date and time (start of current transaction);301         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>302        </p>303        <p>304         <code class="literal">current_timestamp</code>305         → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>306        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">307         <code class="function">current_timestamp</code> ( <code class="type">integer</code> )308         → <code class="returnvalue">timestamp with time zone</code>309        </p>310        <p>311         Current date and time (start of current transaction), with limited precision;312         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>313        </p>314        <p>315         <code class="literal">current_timestamp(0)</code>316         → <code class="returnvalue">2019-12-23 14:39:53-05</code>317        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">318         <a id="id-1.5.8.15.6.2.2.9.1.1.1" class="indexterm"></a>319         <code class="function">date_add</code> ( <code class="type">timestamp with time zone</code>, <code class="type">interval</code> [<span class="optional">, <code class="type">text</code> </span>] )320         → <code class="returnvalue">timestamp with time zone</code>321        </p>322        <p>323         Add an <code class="type">interval</code> to a <code class="type">timestamp with time324         zone</code>, computing times of day and daylight-savings adjustments325         according to the time zone named by the third argument, or the326         current <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting if that is omitted.327         The form with two arguments is equivalent to the <code class="type">timestamp with328         time zone</code> <code class="literal">+</code> <code class="type">interval</code> operator.329        </p>330        <p>331         <code class="literal">date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</code>332         → <code class="returnvalue">2021-10-31 23:00:00+00</code>333        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">334         <code class="function">date_bin</code> ( <code class="type">interval</code>, <code class="type">timestamp</code>, <code class="type">timestamp</code> )335         → <code class="returnvalue">timestamp</code>336        </p>337        <p>338         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>339        </p>340        <p>341         <code class="literal">date_bin('15 minutes', timestamp '2001-02-16 20:38:40', timestamp '2001-02-16 20:05:00')</code>342         → <code class="returnvalue">2001-02-16 20:35:00</code>343        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">344         <a id="id-1.5.8.15.6.2.2.11.1.1.1" class="indexterm"></a>345         <code class="function">date_part</code> ( <code class="type">text</code>, <code class="type">timestamp</code> )346         → <code class="returnvalue">double precision</code>347        </p>348        <p>349         Get timestamp subfield (equivalent to <code class="function">extract</code>);350         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" title="9.9.1. EXTRACT, date_part">Section 9.9.1</a>351        </p>352        <p>353         <code class="literal">date_part('hour', timestamp '2001-02-16 20:38:40')</code>354         → <code class="returnvalue">20</code>355        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">356         <code class="function">date_part</code> ( <code class="type">text</code>, <code class="type">interval</code> )357         → <code class="returnvalue">double precision</code>358        </p>359        <p>360         Get interval subfield (equivalent to <code class="function">extract</code>);361         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" title="9.9.1. EXTRACT, date_part">Section 9.9.1</a>362        </p>363        <p>364         <code class="literal">date_part('month', interval '2 years 3 months')</code>365         → <code class="returnvalue">3</code>366        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">367         <a id="id-1.5.8.15.6.2.2.13.1.1.1" class="indexterm"></a>368         <code class="function">date_subtract</code> ( <code class="type">timestamp with time zone</code>, <code class="type">interval</code> [<span class="optional">, <code class="type">text</code> </span>] )369         → <code class="returnvalue">timestamp with time zone</code>370        </p>371        <p>372         Subtract an <code class="type">interval</code> from a <code class="type">timestamp with time373         zone</code>, computing times of day and daylight-savings adjustments374         according to the time zone named by the third argument, or the375         current <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting if that is omitted.376         The form with two arguments is equivalent to the <code class="type">timestamp with377         time zone</code> <code class="literal">-</code> <code class="type">interval</code> operator.378        </p>379        <p>380         <code class="literal">date_subtract('2021-11-01 00:00:00+01'::timestamptz, '1 day'::interval, 'Europe/Warsaw')</code>381         → <code class="returnvalue">2021-10-30 22:00:00+00</code>382        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">383         <a id="id-1.5.8.15.6.2.2.14.1.1.1" class="indexterm"></a>384         <code class="function">date_trunc</code> ( <code class="type">text</code>, <code class="type">timestamp</code> )385         → <code class="returnvalue">timestamp</code>386        </p>387        <p>388         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>389        </p>390        <p>391         <code class="literal">date_trunc('hour', timestamp '2001-02-16 20:38:40')</code>392         → <code class="returnvalue">2001-02-16 20:00:00</code>393        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">394         <code class="function">date_trunc</code> ( <code class="type">text</code>, <code class="type">timestamp with time zone</code>, <code class="type">text</code> )395         → <code class="returnvalue">timestamp with time zone</code>396        </p>397        <p>398         Truncate to specified precision in the specified time zone; see399         <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-TRUNC" title="9.9.2. date_trunc">Section 9.9.2</a>400        </p>401        <p>402         <code class="literal">date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney')</code>403         → <code class="returnvalue">2001-02-16 13:00:00+00</code>404        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">405         <code class="function">date_trunc</code> ( <code class="type">text</code>, <code class="type">interval</code> )406         → <code class="returnvalue">interval</code>407        </p>408        <p>409         Truncate to specified precision; see410         <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-TRUNC" title="9.9.2. date_trunc">Section 9.9.2</a>411        </p>412        <p>413         <code class="literal">date_trunc('hour', interval '2 days 3 hours 40 minutes')</code>414         → <code class="returnvalue">2 days 03:00:00</code>415        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">416         <a id="id-1.5.8.15.6.2.2.17.1.1.1" class="indexterm"></a>417         <code class="function">extract</code> ( <em class="parameter"><code>field</code></em> <code class="literal">from</code> <code class="type">timestamp</code> )418         → <code class="returnvalue">numeric</code>419        </p>420        <p>421         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>422        </p>423        <p>424         <code class="literal">extract(hour from timestamp '2001-02-16 20:38:40')</code>425         → <code class="returnvalue">20</code>426        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">427         <code class="function">extract</code> ( <em class="parameter"><code>field</code></em> <code class="literal">from</code> <code class="type">interval</code> )428         → <code class="returnvalue">numeric</code>429        </p>430        <p>431         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>432        </p>433        <p>434         <code class="literal">extract(month from interval '2 years 3 months')</code>435         → <code class="returnvalue">3</code>436        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">437         <a id="id-1.5.8.15.6.2.2.19.1.1.1" class="indexterm"></a>438         <code class="function">isfinite</code> ( <code class="type">date</code> )439         → <code class="returnvalue">boolean</code>440        </p>441        <p>442         Test for finite date (not +/-infinity)443        </p>444        <p>445         <code class="literal">isfinite(date '2001-02-16')</code>446         → <code class="returnvalue">true</code>447        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">448         <code class="function">isfinite</code> ( <code class="type">timestamp</code> )449         → <code class="returnvalue">boolean</code>450        </p>451        <p>452         Test for finite timestamp (not +/-infinity)453        </p>454        <p>455         <code class="literal">isfinite(timestamp 'infinity')</code>456         → <code class="returnvalue">false</code>457        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">458         <code class="function">isfinite</code> ( <code class="type">interval</code> )459         → <code class="returnvalue">boolean</code>460        </p>461        <p>462         Test for finite interval (currently always true)463        </p>464        <p>465         <code class="literal">isfinite(interval '4 hours')</code>466         → <code class="returnvalue">true</code>467        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">468         <a id="FUNCTION-JUSTIFY-DAYS" class="indexterm"></a>469         <code class="function">justify_days</code> ( <code class="type">interval</code> )470         → <code class="returnvalue">interval</code>471        </p>472        <p>473         Adjust interval, converting 30-day time periods to months474        </p>475        <p>476         <code class="literal">justify_days(interval '1 year 65 days')</code>477         → <code class="returnvalue">1 year 2 mons 5 days</code>478        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">479         <a id="FUNCTION-JUSTIFY-HOURS" class="indexterm"></a>480         <code class="function">justify_hours</code> ( <code class="type">interval</code> )481         → <code class="returnvalue">interval</code>482        </p>483        <p>484         Adjust interval, converting 24-hour time periods to days485        </p>486        <p>487         <code class="literal">justify_hours(interval '50 hours 10 minutes')</code>488         → <code class="returnvalue">2 days 02:10:00</code>489        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">490         <a id="id-1.5.8.15.6.2.2.24.1.1.1" class="indexterm"></a>491         <code class="function">justify_interval</code> ( <code class="type">interval</code> )492         → <code class="returnvalue">interval</code>493        </p>494        <p>495         Adjust interval using <code class="function">justify_days</code>496         and <code class="function">justify_hours</code>, with additional sign497         adjustments498        </p>499        <p>500         <code class="literal">justify_interval(interval '1 mon -1 hour')</code>501         → <code class="returnvalue">29 days 23:00:00</code>502        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">503         <a id="id-1.5.8.15.6.2.2.25.1.1.1" class="indexterm"></a>504         <code class="function">localtime</code>505         → <code class="returnvalue">time</code>506        </p>507        <p>508         Current time of day;509         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>510        </p>511        <p>512         <code class="literal">localtime</code>513         → <code class="returnvalue">14:39:53.662522</code>514        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">515         <code class="function">localtime</code> ( <code class="type">integer</code> )516         → <code class="returnvalue">time</code>517        </p>518        <p>519         Current time of day, with limited precision;520         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>521        </p>522        <p>523         <code class="literal">localtime(0)</code>524         → <code class="returnvalue">14:39:53</code>525        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">526         <a id="id-1.5.8.15.6.2.2.27.1.1.1" class="indexterm"></a>527         <code class="function">localtimestamp</code>528         → <code class="returnvalue">timestamp</code>529        </p>530        <p>531         Current date and time (start of current transaction);532         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>533        </p>534        <p>535         <code class="literal">localtimestamp</code>536         → <code class="returnvalue">2019-12-23 14:39:53.662522</code>537        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">538         <code class="function">localtimestamp</code> ( <code class="type">integer</code> )539         → <code class="returnvalue">timestamp</code>540        </p>541        <p>542         Current date and time (start of current543         transaction), with limited precision;544         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>545        </p>546        <p>547         <code class="literal">localtimestamp(2)</code>548         → <code class="returnvalue">2019-12-23 14:39:53.66</code>549        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">550         <a id="id-1.5.8.15.6.2.2.29.1.1.1" class="indexterm"></a>551         <code class="function">make_date</code> ( <em class="parameter"><code>year</code></em> <code class="type">int</code>,552         <em class="parameter"><code>month</code></em> <code class="type">int</code>,553         <em class="parameter"><code>day</code></em> <code class="type">int</code> )554         → <code class="returnvalue">date</code>555        </p>556        <p>557         Create date from year, month and day fields558         (negative years signify BC)559        </p>560        <p>561         <code class="literal">make_date(2013, 7, 15)</code>562         → <code class="returnvalue">2013-07-15</code>563        </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>564         <code class="function">make_interval</code> ( [<span class="optional"> <em class="parameter"><code>years</code></em> <code class="type">int</code>565         [<span class="optional">, <em class="parameter"><code>months</code></em> <code class="type">int</code>566         [<span class="optional">, <em class="parameter"><code>weeks</code></em> <code class="type">int</code>567         [<span class="optional">, <em class="parameter"><code>days</code></em> <code class="type">int</code>568         [<span class="optional">, <em class="parameter"><code>hours</code></em> <code class="type">int</code>569         [<span class="optional">, <em class="parameter"><code>mins</code></em> <code class="type">int</code>570         [<span class="optional">, <em class="parameter"><code>secs</code></em> <code class="type">double precision</code>571         </span>]</span>]</span>]</span>]</span>]</span>]</span>] )572         → <code class="returnvalue">interval</code>573        </p>574        <p>575         Create interval from years, months, weeks, days, hours, minutes and576         seconds fields, each of which can default to zero577        </p>578        <p>579         <code class="literal">make_interval(days =&gt; 10)</code>580         → <code class="returnvalue">10 days</code>581        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">582         <a id="id-1.5.8.15.6.2.2.31.1.1.1" class="indexterm"></a>583         <code class="function">make_time</code> ( <em class="parameter"><code>hour</code></em> <code class="type">int</code>,584         <em class="parameter"><code>min</code></em> <code class="type">int</code>,585         <em class="parameter"><code>sec</code></em> <code class="type">double precision</code> )586         → <code class="returnvalue">time</code>587        </p>588        <p>589         Create time from hour, minute and seconds fields590        </p>591        <p>592         <code class="literal">make_time(8, 15, 23.5)</code>593         → <code class="returnvalue">08:15:23.5</code>594        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">595         <a id="id-1.5.8.15.6.2.2.32.1.1.1" class="indexterm"></a>596         <code class="function">make_timestamp</code> ( <em class="parameter"><code>year</code></em> <code class="type">int</code>,597         <em class="parameter"><code>month</code></em> <code class="type">int</code>,598         <em class="parameter"><code>day</code></em> <code class="type">int</code>,599         <em class="parameter"><code>hour</code></em> <code class="type">int</code>,600         <em class="parameter"><code>min</code></em> <code class="type">int</code>,601         <em class="parameter"><code>sec</code></em> <code class="type">double precision</code> )602         → <code class="returnvalue">timestamp</code>603        </p>604        <p>605         Create timestamp from year, month, day, hour, minute and seconds fields606         (negative years signify BC)607        </p>608        <p>609         <code class="literal">make_timestamp(2013, 7, 15, 8, 15, 23.5)</code>610         → <code class="returnvalue">2013-07-15 08:15:23.5</code>611        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">612         <a id="id-1.5.8.15.6.2.2.33.1.1.1" class="indexterm"></a>613         <code class="function">make_timestamptz</code> ( <em class="parameter"><code>year</code></em> <code class="type">int</code>,614         <em class="parameter"><code>month</code></em> <code class="type">int</code>,615         <em class="parameter"><code>day</code></em> <code class="type">int</code>,616         <em class="parameter"><code>hour</code></em> <code class="type">int</code>,617         <em class="parameter"><code>min</code></em> <code class="type">int</code>,618         <em class="parameter"><code>sec</code></em> <code class="type">double precision</code>619         [<span class="optional">, <em class="parameter"><code>timezone</code></em> <code class="type">text</code> </span>] )620         → <code class="returnvalue">timestamp with time zone</code>621        </p>622        <p>623         Create timestamp with time zone from year, month, day, hour, minute624         and seconds fields (negative years signify BC).625         If <em class="parameter"><code>timezone</code></em> is not626         specified, the current time zone is used; the examples assume the627         session time zone is <code class="literal">Europe/London</code>628        </p>629        <p>630         <code class="literal">make_timestamptz(2013, 7, 15, 8, 15, 23.5)</code>631         → <code class="returnvalue">2013-07-15 08:15:23.5+01</code>632        </p>633        <p>634         <code class="literal">make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York')</code>635         → <code class="returnvalue">2013-07-15 13:15:23.5+01</code>636        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">637         <a id="id-1.5.8.15.6.2.2.34.1.1.1" class="indexterm"></a>638         <code class="function">now</code> ( )639         → <code class="returnvalue">timestamp with time zone</code>640        </p>641        <p>642         Current date and time (start of current transaction);643         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>644        </p>645        <p>646         <code class="literal">now()</code>647         → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>648        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">649         <a id="id-1.5.8.15.6.2.2.35.1.1.1" class="indexterm"></a>650         <code class="function">statement_timestamp</code> ( )651         → <code class="returnvalue">timestamp with time zone</code>652        </p>653        <p>654         Current date and time (start of current statement);655         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>656        </p>657        <p>658         <code class="literal">statement_timestamp()</code>659         → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>660        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">661         <a id="id-1.5.8.15.6.2.2.36.1.1.1" class="indexterm"></a>662         <code class="function">timeofday</code> ( )663         → <code class="returnvalue">text</code>664        </p>665        <p>666         Current date and time667         (like <code class="function">clock_timestamp</code>, but as a <code class="type">text</code> string);668         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>669        </p>670        <p>671         <code class="literal">timeofday()</code>672         → <code class="returnvalue">Mon Dec 23 14:39:53.662522 2019 EST</code>673        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">674         <a id="id-1.5.8.15.6.2.2.37.1.1.1" class="indexterm"></a>675         <code class="function">transaction_timestamp</code> ( )676         → <code class="returnvalue">timestamp with time zone</code>677        </p>678        <p>679         Current date and time (start of current transaction);680         see <a class="xref" href="functions-datetime.html#FUNCTIONS-DATETIME-CURRENT" title="9.9.5. Current Date/Time">Section 9.9.5</a>681        </p>682        <p>683         <code class="literal">transaction_timestamp()</code>684         → <code class="returnvalue">2019-12-23 14:39:53.662522-05</code>685        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">686         <a id="id-1.5.8.15.6.2.2.38.1.1.1" class="indexterm"></a>687         <code class="function">to_timestamp</code> ( <code class="type">double precision</code> )688         → <code class="returnvalue">timestamp with time zone</code>689        </p>690        <p>691         Convert Unix epoch (seconds since 1970-01-01 00:00:00+00) to692         timestamp with time zone693        </p>694        <p>695         <code class="literal">to_timestamp(1284352323)</code>696         → <code class="returnvalue">2010-09-13 04:32:03+00</code>697        </p></td></tr></tbody></table></div></div><br class="table-break" /><p>698    <a id="id-1.5.8.15.7.1" class="indexterm"></a>699    In addition to these functions, the SQL <code class="literal">OVERLAPS</code> operator is700    supported:701</p><pre class="synopsis">702(<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>)703(<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>)704</pre><p>705    This expression yields true when two time periods (defined by their706    endpoints) overlap, false when they do not overlap.  The endpoints707    can be specified as pairs of dates, times, or time stamps; or as708    a date, time, or time stamp followed by an interval.  When a pair709    of values is provided, either the start or the end can be written710    first; <code class="literal">OVERLAPS</code> automatically takes the earlier value711    of the pair as the start.  Each time period is considered to712    represent the half-open interval <em class="replaceable"><code>start</code></em> <code class="literal">&lt;=</code>713    <em class="replaceable"><code>time</code></em> <code class="literal">&lt;</code> <em class="replaceable"><code>end</code></em>, unless714    <em class="replaceable"><code>start</code></em> and <em class="replaceable"><code>end</code></em> are equal in which case it715    represents that single time instant.  This means for instance that two716    time periods with only an endpoint in common do not overlap.717   </p><pre class="screen">718SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS719       (DATE '2001-10-30', DATE '2002-10-30');720<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">true</code>721SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS722       (DATE '2001-10-30', DATE '2002-10-30');723<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">false</code>724SELECT (DATE '2001-10-29', DATE '2001-10-30') OVERLAPS725       (DATE '2001-10-30', DATE '2001-10-31');726<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">false</code>727SELECT (DATE '2001-10-30', DATE '2001-10-30') OVERLAPS728       (DATE '2001-10-30', DATE '2001-10-31');729<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">true</code>730</pre><p>731   When adding an <code class="type">interval</code> value to (or subtracting an732   <code class="type">interval</code> value from) a <code class="type">timestamp</code>733   or <code class="type">timestamp with time zone</code> value, the months, days, and734   microseconds fields of the <code class="type">interval</code> value are handled in turn.735   First, a nonzero months field advances or decrements the date of the736   timestamp by the indicated number of months, keeping the day of month the737   same unless it would be past the end of the new month, in which case the738   last day of that month is used.  (For example, March 31 plus 1 month739   becomes April 30, but March 31 plus 2 months becomes May 31.)740   Then the days field advances or decrements the date of the timestamp by741   the indicated number of days.  In both these steps the local time of day742   is kept the same.  Finally, if there is a nonzero microseconds field, it743   is added or subtracted literally.744   When doing arithmetic on a <code class="type">timestamp with time zone</code> value in745   a time zone that recognizes DST, this means that adding or subtracting746   (say) <code class="literal">interval '1 day'</code> does not necessarily have the747   same result as adding or subtracting <code class="literal">interval '24748   hours'</code>.749   For example, with the session time zone set750   to <code class="literal">America/Denver</code>:751</p><pre class="screen">752SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';753<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2005-04-03 12:00:00-06</code>754SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '24 hours';755<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2005-04-03 13:00:00-06</code>756</pre><p>757   This happens because an hour was skipped due to a change in daylight saving758   time at <code class="literal">2005-04-03 02:00:00</code> in time zone759   <code class="literal">America/Denver</code>.760  </p><p>761   Note there can be ambiguity in the <code class="literal">months</code> field returned by762   <code class="function">age</code> because different months have different numbers of763   days.  <span class="productname">PostgreSQL</span>'s approach uses the month from the764   earlier of the two dates when calculating partial months.  For example,765   <code class="literal">age('2004-06-01', '2004-04-30')</code> uses April to yield766   <code class="literal">1 mon 1 day</code>, while using May would yield <code class="literal">1 mon 2767   days</code> because May has 31 days, while April has only 30.768  </p><p>769   Subtraction of dates and timestamps can also be complex.  One conceptually770   simple way to perform subtraction is to convert each value to a number771   of seconds using <code class="literal">EXTRACT(EPOCH FROM ...)</code>, then subtract the772   results; this produces the773   number of <span class="emphasis"><em>seconds</em></span> between the two values.  This will adjust774   for the number of days in each month, timezone changes, and daylight775   saving time adjustments.  Subtraction of date or timestamp776   values with the <span class="quote">“<span class="quote"><code class="literal">-</code></span>”</span> operator777   returns the number of days (24-hours) and hours/minutes/seconds778   between the values, making the same adjustments.  The <code class="function">age</code>779   function returns years, months, days, and hours/minutes/seconds,780   performing field-by-field subtraction and then adjusting for negative781   field values.  The following queries illustrate the differences in these782   approaches.  The sample results were produced with <code class="literal">timezone783   = 'US/Eastern'</code>; there is a daylight saving time change between the784   two dates used:785  </p><pre class="screen">786SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -787       EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');788<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">10537200.000000</code>789SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -790        EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))791        / 60 / 60 / 24;792<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">121.9583333333333333</code>793SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';794<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">121 days 23:00:00</code>795SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');796<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">4 mons</code>797</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> <a href="#FUNCTIONS-DATETIME-EXTRACT" class="id_link">#</a></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">798EXTRACT(<em class="replaceable"><code>field</code></em> FROM <em class="replaceable"><code>source</code></em>)799</pre><p>800    The <code class="function">extract</code> function retrieves subfields801    such as year or hour from date/time values.802    <em class="replaceable"><code>source</code></em> must be a value expression of803    type <code class="type">timestamp</code>, <code class="type">date</code>, <code class="type">time</code>,804    or <code class="type">interval</code>.  (Timestamps and times can be with or805    without time zone.)806    <em class="replaceable"><code>field</code></em> is an identifier or807    string that selects what field to extract from the source value.808    Not all fields are valid for every input data type; for example, fields809    smaller than a day cannot be extracted from a <code class="type">date</code>, while810    fields of a day or more cannot be extracted from a <code class="type">time</code>.811    The <code class="function">extract</code> function returns values of type812    <code class="type">numeric</code>.813   </p><p>814    The following are valid field names:815 816    817    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">century</code></span></dt><dd><p>818        The century; for <code class="type">interval</code> values, the year field819        divided by 100820       </p><pre class="screen">821SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');822<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">20</code>823SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');824<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">21</code>825SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');826<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">1</code>827SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');828<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">-1</code>829SELECT EXTRACT(CENTURY FROM INTERVAL '2001 years');830<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">20</code>831</pre></dd><dt><span class="term"><code class="literal">day</code></span></dt><dd><p>832        The day of the month (1–31); for <code class="type">interval</code>833        values, the number of days834       </p><pre class="screen">835SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');836<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">16</code>837SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');838<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">40</code>839</pre></dd><dt><span class="term"><code class="literal">decade</code></span></dt><dd><p>840        The year field divided by 10841       </p><pre class="screen">842SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');843<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">200</code>844</pre></dd><dt><span class="term"><code class="literal">dow</code></span></dt><dd><p>845        The day of the week as Sunday (<code class="literal">0</code>) to846        Saturday (<code class="literal">6</code>)847       </p><pre class="screen">848SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');849<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">5</code>850</pre><p>851        Note that <code class="function">extract</code>'s day of the week numbering852        differs from that of the <code class="function">to_char(...,853        'D')</code> function.854       </p></dd><dt><span class="term"><code class="literal">doy</code></span></dt><dd><p>855        The day of the year (1–365/366)856       </p><pre class="screen">857SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');858<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">47</code>859</pre></dd><dt><span class="term"><code class="literal">epoch</code></span></dt><dd><p>860        For <code class="type">timestamp with time zone</code> values, the861        number of seconds since 1970-01-01 00:00:00 UTC (negative for862        timestamps before that);863        for <code class="type">date</code> and <code class="type">timestamp</code> values, the864        nominal number of seconds since 1970-01-01 00:00:00,865        without regard to timezone or daylight-savings rules;866        for <code class="type">interval</code> values, the total number867        of seconds in the interval868       </p><pre class="screen">869SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');870<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">982384720.120000</code>871SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12');872<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">982355920.120000</code>873SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');874<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">442800.000000</code>875</pre><p>876        You can convert an epoch value back to a <code class="type">timestamp with time zone</code>877        with <code class="function">to_timestamp</code>:878       </p><pre class="screen">879SELECT to_timestamp(982384720.12);880<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-17 04:38:40.12+00</code>881</pre><p>882        Beware that applying <code class="function">to_timestamp</code> to an epoch883        extracted from a <code class="type">date</code> or <code class="type">timestamp</code> value884        could produce a misleading result: the result will effectively885        assume that the original value had been given in UTC, which might886        not be the case.887       </p></dd><dt><span class="term"><code class="literal">hour</code></span></dt><dd><p>888        The hour field (0–23 in timestamps, unrestricted in889        intervals)890       </p><pre class="screen">891SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');892<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">20</code>893</pre></dd><dt><span class="term"><code class="literal">isodow</code></span></dt><dd><p>894        The day of the week as Monday (<code class="literal">1</code>) to895        Sunday (<code class="literal">7</code>)896       </p><pre class="screen">897SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');898<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">7</code>899</pre><p>900        This is identical to <code class="literal">dow</code> except for Sunday.  This901        matches the <acronym class="acronym">ISO</acronym> 8601 day of the week numbering.902       </p></dd><dt><span class="term"><code class="literal">isoyear</code></span></dt><dd><p>903        The <acronym class="acronym">ISO</acronym> 8601 week-numbering year that the date904        falls in905       </p><pre class="screen">906SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');907<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2005</code>908SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');909<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2006</code>910</pre><p>911        Each <acronym class="acronym">ISO</acronym> 8601 week-numbering year begins with the912        Monday of the week containing the 4th of January, so in early913        January or late December the <acronym class="acronym">ISO</acronym> year may be914        different from the Gregorian year.  See the <code class="literal">week</code>915        field for more information.916       </p></dd><dt><span class="term"><code class="literal">julian</code></span></dt><dd><p>917        The <em class="firstterm">Julian Date</em> corresponding to the918        date or timestamp.  Timestamps919        that are not local midnight result in a fractional value.  See920        <a class="xref" href="datetime-julian-dates.html" title="B.7. Julian Dates">Section B.7</a> for more information.921       </p><pre class="screen">922SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');923<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2453737</code>924SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');925<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2453737.50000000000000000000</code>926</pre></dd><dt><span class="term"><code class="literal">microseconds</code></span></dt><dd><p>927        The seconds field, including fractional parts, multiplied by 1928        000 000;  note that this includes full seconds929       </p><pre class="screen">930SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');931<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">28500000</code>932</pre></dd><dt><span class="term"><code class="literal">millennium</code></span></dt><dd><p>933        The millennium; for <code class="type">interval</code> values, the year field934        divided by 1000935       </p><pre class="screen">936SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');937<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">3</code>938SELECT EXTRACT(MILLENNIUM FROM INTERVAL '2001 years');939<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2</code>940</pre><p>941        Years in the 1900s are in the second millennium.942        The third millennium started January 1, 2001.943       </p></dd><dt><span class="term"><code class="literal">milliseconds</code></span></dt><dd><p>944        The seconds field, including fractional parts, multiplied by945        1000.  Note that this includes full seconds.946       </p><pre class="screen">947SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');948<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">28500.000</code>949</pre></dd><dt><span class="term"><code class="literal">minute</code></span></dt><dd><p>950        The minutes field (0–59)951       </p><pre class="screen">952SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');953<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">38</code>954</pre></dd><dt><span class="term"><code class="literal">month</code></span></dt><dd><p>955        The number of the month within the year (1–12);956        for <code class="type">interval</code> values, the number of months modulo 12957        (0–11)958       </p><pre class="screen">959SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');960<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2</code>961SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');962<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">3</code>963SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');964<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">1</code>965</pre></dd><dt><span class="term"><code class="literal">quarter</code></span></dt><dd><p>966        The quarter of the year (1–4) that the date is in967       </p><pre class="screen">968SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');969<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">1</code>970</pre></dd><dt><span class="term"><code class="literal">second</code></span></dt><dd><p>971        The seconds field, including any fractional seconds972       </p><pre class="screen">973SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40');974<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">40.000000</code>975SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');976<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">28.500000</code>977</pre></dd><dt><span class="term"><code class="literal">timezone</code></span></dt><dd><p>978        The time zone offset from UTC, measured in seconds.  Positive values979        correspond to time zones east of UTC, negative values to980        zones west of UTC.  (Technically,981        <span class="productname">PostgreSQL</span> does not use UTC because982        leap seconds are not handled.)983       </p></dd><dt><span class="term"><code class="literal">timezone_hour</code></span></dt><dd><p>984        The hour component of the time zone offset985       </p></dd><dt><span class="term"><code class="literal">timezone_minute</code></span></dt><dd><p>986        The minute component of the time zone offset987       </p></dd><dt><span class="term"><code class="literal">week</code></span></dt><dd><p>988        The number of the <acronym class="acronym">ISO</acronym> 8601 week-numbering week of989        the year.  By definition, ISO weeks start on Mondays and the first990        week of a year contains January 4 of that year.  In other words, the991        first Thursday of a year is in week 1 of that year.992       </p><p>993        In the ISO week-numbering system, it is possible for early-January994        dates to be part of the 52nd or 53rd week of the previous year, and for995        late-December dates to be part of the first week of the next year.996        For example, <code class="literal">2005-01-01</code> is part of the 53rd week of year997        2004, and <code class="literal">2006-01-01</code> is part of the 52nd week of year998        2005, while <code class="literal">2012-12-31</code> is part of the first week of 2013.999        It's recommended to use the <code class="literal">isoyear</code> field together with1000        <code class="literal">week</code> to get consistent results.1001       </p><pre class="screen">1002SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');1003<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">7</code>1004</pre></dd><dt><span class="term"><code class="literal">year</code></span></dt><dd><p>1005        The year field.  Keep in mind there is no <code class="literal">0 AD</code>, so subtracting1006        <code class="literal">BC</code> years from <code class="literal">AD</code> years should be done with care.1007       </p><pre class="screen">1008SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');1009<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001</code>1010</pre></dd></dl></div><p>1011   </p><p>1012    When processing an <code class="type">interval</code> value,1013    the <code class="function">extract</code> function produces field values that1014    match the interpretation used by the interval output function.  This1015    can produce surprising results if one starts with a non-normalized1016    interval representation, for example:1017</p><pre class="screen">1018SELECT INTERVAL '80 minutes';1019<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">01:20:00</code>1020SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');1021<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">20</code>1022</pre><p>1023   </p><div class="note"><h3 class="title">Note</h3><p>1024     When the input value is +/-Infinity, <code class="function">extract</code> returns1025     +/-Infinity for monotonically-increasing fields (<code class="literal">epoch</code>,1026     <code class="literal">julian</code>, <code class="literal">year</code>, <code class="literal">isoyear</code>,1027     <code class="literal">decade</code>, <code class="literal">century</code>, and <code class="literal">millennium</code>).1028     For other fields, NULL is returned.  <span class="productname">PostgreSQL</span>1029     versions before 9.6 returned zero for all cases of infinite input.1030    </p></div><p>1031    The <code class="function">extract</code> function is primarily intended1032    for computational processing.  For formatting date/time values for1033    display, see <a class="xref" href="functions-formatting.html" title="9.8. Data Type Formatting Functions">Section 9.8</a>.1034   </p><p>1035    The <code class="function">date_part</code> function is modeled on the traditional1036    <span class="productname">Ingres</span> equivalent to the1037    <acronym class="acronym">SQL</acronym>-standard function <code class="function">extract</code>:1038</p><pre class="synopsis">1039date_part('<em class="replaceable"><code>field</code></em>', <em class="replaceable"><code>source</code></em>)1040</pre><p>1041    Note that here the <em class="replaceable"><code>field</code></em> parameter needs to1042    be a string value, not a name.  The valid field names for1043    <code class="function">date_part</code> are the same as for1044    <code class="function">extract</code>.1045    For historical reasons, the <code class="function">date_part</code> function1046    returns values of type <code class="type">double precision</code>.  This can result in1047    a loss of precision in certain uses.  Using <code class="function">extract</code>1048    is recommended instead.1049   </p><pre class="screen">1050SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');1051<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">16</code>1052SELECT date_part('hour', INTERVAL '4 hours 3 minutes');1053<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">4</code>1054</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> <a href="#FUNCTIONS-DATETIME-TRUNC" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.15.14.2" class="indexterm"></a><p>1055    The function <code class="function">date_trunc</code> is conceptually1056    similar to the <code class="function">trunc</code> function for numbers.1057   </p><p>1058</p><pre class="synopsis">1059date_trunc(<em class="replaceable"><code>field</code></em>, <em class="replaceable"><code>source</code></em> [, <em class="replaceable"><code>time_zone</code></em> ])1060</pre><p>1061    <em class="replaceable"><code>source</code></em> is a value expression of type1062    <code class="type">timestamp</code>, <code class="type">timestamp with time zone</code>,1063    or <code class="type">interval</code>.1064    (Values of type <code class="type">date</code> and1065    <code class="type">time</code> are cast automatically to <code class="type">timestamp</code> or1066    <code class="type">interval</code>, respectively.)1067    <em class="replaceable"><code>field</code></em> selects to which precision to1068    truncate the input value.  The return value is likewise of type1069    <code class="type">timestamp</code>, <code class="type">timestamp with time zone</code>,1070    or <code class="type">interval</code>,1071    and it has all fields that are less significant than the1072    selected one set to zero (or one, for day and month).1073   </p><p>1074    Valid values for <em class="replaceable"><code>field</code></em> are:1075    </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>1076   </p><p>1077    When the input value is of type <code class="type">timestamp with time zone</code>,1078    the truncation is performed with respect to a particular time zone;1079    for example, truncation to <code class="literal">day</code> produces a value that1080    is midnight in that zone.  By default, truncation is done with respect1081    to the current <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting, but the1082    optional <em class="replaceable"><code>time_zone</code></em> argument can be provided1083    to specify a different time zone.  The time zone name can be specified1084    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>.1085   </p><p>1086    A time zone cannot be specified when processing <code class="type">timestamp without1087    time zone</code> or <code class="type">interval</code> inputs.  These are always1088    taken at face value.1089   </p><p>1090    Examples (assuming the local time zone is <code class="literal">America/New_York</code>):1091</p><pre class="screen">1092SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');1093<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 20:00:00</code>1094SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');1095<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-01-01 00:00:00</code>1096SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00');1097<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 00:00:00-05</code>1098SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00', 'Australia/Sydney');1099<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 08:00:00-05</code>1100SELECT date_trunc('hour', INTERVAL '3 days 02:47:33');1101<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">3 days 02:00:00</code>1102</pre><p>1103   </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> <a href="#FUNCTIONS-DATETIME-BIN" class="id_link">#</a></h3></div></div></div><a id="id-1.5.8.15.15.2" class="indexterm"></a><p>1104    The function <code class="function">date_bin</code> <span class="quote">“<span class="quote">bins</span>”</span> the input1105    timestamp into the specified interval (the <em class="firstterm">stride</em>)1106    aligned with a specified origin.1107   </p><p>1108</p><pre class="synopsis">1109date_bin(<em class="replaceable"><code>stride</code></em>, <em class="replaceable"><code>source</code></em>, <em class="replaceable"><code>origin</code></em>)1110</pre><p>1111    <em class="replaceable"><code>source</code></em> is a value expression of type1112    <code class="type">timestamp</code> or <code class="type">timestamp with time zone</code>.  (Values1113    of type <code class="type">date</code> are cast automatically to1114    <code class="type">timestamp</code>.)  <em class="replaceable"><code>stride</code></em> is a value1115    expression of type <code class="type">interval</code>.  The return value is likewise1116    of type <code class="type">timestamp</code> or <code class="type">timestamp with time zone</code>,1117    and it marks the beginning of the bin into which the1118    <em class="replaceable"><code>source</code></em> is placed.1119   </p><p>1120    Examples:1121</p><pre class="screen">1122SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');1123<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2020-02-11 15:30:00</code>1124SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');1125<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2020-02-11 15:32:30</code>1126</pre><p>1127   </p><p>1128    In the case of full units (1 minute, 1 hour, etc.), it gives the same result as1129    the analogous <code class="function">date_trunc</code> call, but the difference is1130    that <code class="function">date_bin</code> can truncate to an arbitrary interval.1131   </p><p>1132    The <em class="parameter"><code>stride</code></em> interval must be greater than zero and1133    cannot contain units of month or larger.1134   </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> <a href="#FUNCTIONS-DATETIME-ZONECONVERT" class="id_link">#</a></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>1135    The <code class="literal">AT TIME ZONE</code> operator converts time1136    stamp <span class="emphasis"><em>without</em></span> time zone to/from1137    time stamp <span class="emphasis"><em>with</em></span> time zone, and1138    <code class="type">time with time zone</code> values to different time1139    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 its1140    variants.1141   </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">1142         Operator1143        </p>1144        <p>1145         Description1146        </p>1147        <p>1148         Example(s)1149        </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature">1150         <code class="type">timestamp without time zone</code> <code class="literal">AT TIME ZONE</code> <em class="replaceable"><code>zone</code></em>1151         → <code class="returnvalue">timestamp with time zone</code>1152        </p>1153        <p>1154         Converts given time stamp <span class="emphasis"><em>without</em></span> time zone to1155         time stamp <span class="emphasis"><em>with</em></span> time zone, assuming the given1156         value is in the named time zone.1157        </p>1158        <p>1159         <code class="literal">timestamp '2001-02-16 20:38:40' at time zone 'America/Denver'</code>1160         → <code class="returnvalue">2001-02-17 03:38:40+00</code>1161        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1162         <code class="type">timestamp with time zone</code> <code class="literal">AT TIME ZONE</code> <em class="replaceable"><code>zone</code></em>1163         → <code class="returnvalue">timestamp without time zone</code>1164        </p>1165        <p>1166         Converts given time stamp <span class="emphasis"><em>with</em></span> time zone to1167         time stamp <span class="emphasis"><em>without</em></span> time zone, as the time would1168         appear in that zone.1169        </p>1170        <p>1171         <code class="literal">timestamp with time zone '2001-02-16 20:38:40-05' at time zone 'America/Denver'</code>1172         → <code class="returnvalue">2001-02-16 18:38:40</code>1173        </p></td></tr><tr><td class="func_table_entry"><p class="func_signature">1174         <code class="type">time with time zone</code> <code class="literal">AT TIME ZONE</code> <em class="replaceable"><code>zone</code></em>1175         → <code class="returnvalue">time with time zone</code>1176        </p>1177        <p>1178         Converts given time <span class="emphasis"><em>with</em></span> time zone to a new time1179         zone.  Since no date is supplied, this uses the currently active UTC1180         offset for the named destination zone.1181        </p>1182        <p>1183         <code class="literal">time with time zone '05:34:17-05' at time zone 'UTC'</code>1184         → <code class="returnvalue">10:34:17+00</code>1185        </p></td></tr></tbody></table></div></div><br class="table-break" /><p>1186    In these expressions, the desired time zone <em class="replaceable"><code>zone</code></em> can be1187    specified either as a text value (e.g., <code class="literal">'America/Los_Angeles'</code>)1188    or as an interval (e.g., <code class="literal">INTERVAL '-08:00'</code>).1189    In the text case, a time zone name can be specified in any of the ways1190    described in <a class="xref" href="datatype-datetime.html#DATATYPE-TIMEZONES" title="8.5.3. Time Zones">Section 8.5.3</a>.1191    The interval case is only useful for zones that have fixed offsets from1192    UTC, so it is not very common in practice.1193   </p><p>1194    Examples (assuming the current <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a> setting1195    is <code class="literal">America/Los_Angeles</code>):1196</p><pre class="screen">1197SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';1198<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 19:38:40-08</code>1199SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';1200<em class="lineannotation"><span class="lineannotation">Result: </span></em><code class="computeroutput">2001-02-16 18:38:40</code>

Showing the first 1,200 of 1357 lines. Download the file for the rest.

codekingpro/portable-devtools · Team Ai