codekingpro/portable-devtools
115k
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 => 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"><=</code>713 <em class="replaceable"><code>time</code></em> <code class="literal"><</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>