date-time.asciidoc 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783
  1. [role="xpack"]
  2. [testenv="basic"]
  3. [[sql-functions-datetime]]
  4. === Date/Time and Interval Functions and Operators
  5. {es-sql} offers a wide range of facilities for performing date/time manipulations.
  6. [[sql-functions-datetime-interval]]
  7. ==== Intervals
  8. A common requirement when dealing with date/time in general revolves around
  9. the notion of `interval`, a topic that is worth exploring in the context of {es} and {es-sql}.
  10. {es} has comprehensive support for <<date-math, date math>> both inside <<date-math-index-names, index names>> and <<mapping-date-format, queries>>.
  11. Inside {es-sql} the former is supported as is by passing the expression in the table name, while the latter is supported through the standard SQL `INTERVAL`.
  12. The table below shows the mapping between {es} and {es-sql}:
  13. [cols="^m,^m"]
  14. |==========================
  15. s|{es}
  16. s|{es-sql}
  17. 2+h| Index/Table datetime math
  18. 2+|<index-{now/M{YYYY.MM}}>
  19. 2+h| Query date/time math
  20. | 1y | INTERVAL 1 YEAR
  21. | 2M | INTERVAL 2 MONTH
  22. | 3w | INTERVAL 21 DAY
  23. | 4d | INTERVAL 4 DAY
  24. | 5h | INTERVAL 5 HOUR
  25. | 6m | INTERVAL 6 MINUTE
  26. | 7s | INTERVAL 7 SECOND
  27. |==========================
  28. `INTERVAL` allows either `YEAR` and `MONTH` to be mixed together _or_ `DAY`, `HOUR`, `MINUTE` and `SECOND`.
  29. TIP: {es-sql} accepts also the plural for each time unit (e.g. both `YEAR` and `YEARS` are valid).
  30. Example of the possible combinations below:
  31. [cols="^,^"]
  32. |===
  33. s|Interval
  34. s|Description
  35. | `INTERVAL '1-2' YEAR TO MONTH` | 1 year and 2 months
  36. | `INTERVAL '3 4' DAYS TO HOURS` | 3 days and 4 hours
  37. | `INTERVAL '5 6:12' DAYS TO MINUTES` | 5 days, 6 hours and 12 minutes
  38. | `INTERVAL '3 4:56:01' DAY TO SECOND` | 3 days, 4 hours, 56 minutes and 1 second
  39. | `INTERVAL '2 3:45:01.23456789' DAY TO SECOND` | 2 days, 3 hours, 45 minutes, 1 second and 234567890 nanoseconds
  40. | `INTERVAL '123:45' HOUR TO MINUTES` | 123 hours and 45 minutes
  41. | `INTERVAL '65:43:21.0123' HOUR TO SECONDS` | 65 hours, 43 minutes, 21 seconds and 12300000 nanoseconds
  42. | `INTERVAL '45:01.23' MINUTES TO SECONDS` | 45 minutes, 1 second and 230000000 nanoseconds
  43. |===
  44. ==== Operators
  45. Basic arithmetic operators (`+`, `-`, etc) support date/time parameters as indicated below:
  46. [source, sql]
  47. --------------------------------------------------
  48. include-tagged::{sql-specs}/docs/docs.csv-spec[dtIntervalPlusInterval]
  49. --------------------------------------------------
  50. [source, sql]
  51. --------------------------------------------------
  52. include-tagged::{sql-specs}/docs/docs.csv-spec[dtDateTimePlusInterval]
  53. --------------------------------------------------
  54. [source, sql]
  55. --------------------------------------------------
  56. include-tagged::{sql-specs}/docs/docs.csv-spec[dtMinusInterval]
  57. --------------------------------------------------
  58. [source, sql]
  59. --------------------------------------------------
  60. include-tagged::{sql-specs}/docs/docs.csv-spec[dtIntervalMinusInterval]
  61. --------------------------------------------------
  62. [source, sql]
  63. --------------------------------------------------
  64. include-tagged::{sql-specs}/docs/docs.csv-spec[dtDateTimeMinusInterval]
  65. --------------------------------------------------
  66. [source, sql]
  67. --------------------------------------------------
  68. include-tagged::{sql-specs}/docs/docs.csv-spec[dtIntervalMul]
  69. --------------------------------------------------
  70. ==== Functions
  71. Functions that target date/time.
  72. [[sql-functions-current-date]]
  73. ==== `CURRENT_DATE/CURDATE`
  74. .Synopsis:
  75. [source, sql]
  76. --------------------------------------------------
  77. CURRENT_DATE
  78. CURRENT_DATE()
  79. CURDATE()
  80. --------------------------------------------------
  81. *Input*: _none_
  82. *Output*: date
  83. .Description:
  84. Returns the date (no time part) when the current query reached the server.
  85. It can be used both as a keyword: `CURRENT_DATE` or as a function with no arguments: `CURRENT_DATE()`.
  86. [NOTE]
  87. Unlike CURRENT_DATE, `CURDATE()` can only be used as a function with no arguments and not as a keyword.
  88. This method always returns the same value for its every occurrence within the same query.
  89. [source, sql]
  90. --------------------------------------------------
  91. include-tagged::{sql-specs}/docs/docs.csv-spec[currentDate]
  92. --------------------------------------------------
  93. [source, sql]
  94. --------------------------------------------------
  95. include-tagged::{sql-specs}/docs/docs.csv-spec[currentDateFunction]
  96. --------------------------------------------------
  97. [source, sql]
  98. --------------------------------------------------
  99. include-tagged::{sql-specs}/docs/docs.csv-spec[curDateFunction]
  100. --------------------------------------------------
  101. Typically, this function (as well as its twin <<sql-functions-today,TODAY())>> function
  102. is used for relative date filtering:
  103. [source, sql]
  104. --------------------------------------------------
  105. include-tagged::{sql-specs}/docs/docs.csv-spec[filterToday]
  106. --------------------------------------------------
  107. [[sql-functions-current-time]]
  108. ==== `CURRENT_TIME/CURTIME`
  109. .Synopsis:
  110. [source, sql]
  111. --------------------------------------------------
  112. CURRENT_TIME
  113. CURRENT_TIME([precision]) <1>
  114. CURTIME
  115. --------------------------------------------------
  116. *Input*:
  117. <1> fractional digits; optional
  118. *Output*: time
  119. .Description:
  120. Returns the time when the current query reached the server.
  121. As a function, `CURRENT_TIME()` accepts _precision_ as an optional
  122. parameter for rounding the second fractional digits (nanoseconds). The default _precision_ is 3,
  123. meaning a milliseconds precision current time will be returned.
  124. This method always returns the same value for its every occurrence within the same query.
  125. [source, sql]
  126. --------------------------------------------------
  127. include-tagged::{sql-specs}/docs/docs.csv-spec[currentTime]
  128. --------------------------------------------------
  129. [source, sql]
  130. --------------------------------------------------
  131. include-tagged::{sql-specs}/docs/docs.csv-spec[currentTimeFunction]
  132. --------------------------------------------------
  133. [source, sql]
  134. --------------------------------------------------
  135. include-tagged::{sql-specs}/docs/docs.csv-spec[curTimeFunction]
  136. --------------------------------------------------
  137. [source, sql]
  138. --------------------------------------------------
  139. include-tagged::{sql-specs}/docs/docs.csv-spec[currentTimeFunctionPrecision]
  140. --------------------------------------------------
  141. Typically, this function is used for relative date/time filtering:
  142. [source, sql]
  143. --------------------------------------------------
  144. include-tagged::{sql-specs}/docs/docs.csv-spec[filterCurrentTime]
  145. --------------------------------------------------
  146. [IMPORTANT]
  147. Currently, using a _precision_ greater than 3 doesn't make any difference to the output of the
  148. function as the maximum number of second fractional digits returned is 3 (milliseconds).
  149. [[sql-functions-current-timestamp]]
  150. ==== `CURRENT_TIMESTAMP`
  151. .Synopsis:
  152. [source, sql]
  153. --------------------------------------------------
  154. CURRENT_TIMESTAMP
  155. CURRENT_TIMESTAMP([precision]) <1>
  156. --------------------------------------------------
  157. *Input*:
  158. <1> fractional digits; optional
  159. *Output*: date/time
  160. .Description:
  161. Returns the date/time when the current query reached the server.
  162. As a function, `CURRENT_TIMESTAMP()` accepts _precision_ as an optional
  163. parameter for rounding the second fractional digits (nanoseconds). The default _precision_ is 3,
  164. meaning a milliseconds precision current date/time will be returned.
  165. This method always returns the same value for its every occurrence within the same query.
  166. [source, sql]
  167. --------------------------------------------------
  168. include-tagged::{sql-specs}/docs/docs.csv-spec[curTs]
  169. --------------------------------------------------
  170. [source, sql]
  171. --------------------------------------------------
  172. include-tagged::{sql-specs}/docs/docs.csv-spec[curTsFunction]
  173. --------------------------------------------------
  174. [source, sql]
  175. --------------------------------------------------
  176. include-tagged::{sql-specs}/docs/docs.csv-spec[curTsFunctionPrecision]
  177. --------------------------------------------------
  178. Typically, this function (as well as its twin <<sql-functions-now,NOW())>> function is used for
  179. relative date/time filtering:
  180. [source, sql]
  181. --------------------------------------------------
  182. include-tagged::{sql-specs}/docs/docs.csv-spec[filterNow]
  183. --------------------------------------------------
  184. [IMPORTANT]
  185. Currently, using a _precision_ greater than 3 doesn't make any difference to the output of the
  186. function as the maximum number of second fractional digits returned is 3 (milliseconds).
  187. [[sql-functions-datetime-trunc]]
  188. ==== `DATE_TRUNC`
  189. .Synopsis:
  190. [source, sql]
  191. --------------------------------------------------
  192. DATE_TRUNC(
  193. string_exp, <1>
  194. datetime_exp) <2>
  195. --------------------------------------------------
  196. *Input*:
  197. <1> string expression denoting the unit to which the date/datetime should be truncated to
  198. <2> date/datetime expression
  199. *Output*: datetime
  200. .Description:
  201. Truncate the date/datetime to the specified unit by setting all fields that are less significant than the specified
  202. one to zero (or one, for day, day of week and month).
  203. [cols="^,^"]
  204. |===
  205. 2+h|Datetime truncation units
  206. s|unit
  207. s|abbreviations
  208. | millennium | millennia
  209. | century | centuries
  210. | decade | decades
  211. | year | years, yy, yyyy
  212. | quarter | quarters, qq, q
  213. | month | months, mm, m
  214. | week | weeks, wk, ww
  215. | day | days, dd, d
  216. | hour | hours, hh
  217. | minute | minutes, mi, n
  218. | second | seconds, ss, s
  219. | millisecond | milliseconds, ms
  220. | microsecond | microseconds, mcs
  221. | nanosecond | nanoseconds, ns
  222. |===
  223. [source, sql]
  224. --------------------------------------------------
  225. include-tagged::{sql-specs}/docs/docs.csv-spec[truncateDateTimeMillennium]
  226. --------------------------------------------------
  227. [source, sql]
  228. --------------------------------------------------
  229. include-tagged::{sql-specs}/docs/docs.csv-spec[truncateDateTimeWeek]
  230. --------------------------------------------------
  231. [source, sql]
  232. --------------------------------------------------
  233. include-tagged::{sql-specs}/docs/docs.csv-spec[truncateDateTimeMinutes]
  234. --------------------------------------------------
  235. [source, sql]
  236. --------------------------------------------------
  237. include-tagged::{sql-specs}/docs/docs.csv-spec[truncateDateDecades]
  238. --------------------------------------------------
  239. [source, sql]
  240. --------------------------------------------------
  241. include-tagged::{sql-specs}/docs/docs.csv-spec[truncateDateQuarter]
  242. --------------------------------------------------
  243. [[sql-functions-datetime-day]]
  244. ==== `DAY_OF_MONTH/DOM/DAY`
  245. .Synopsis:
  246. [source, sql]
  247. --------------------------------------------------
  248. DAY_OF_MONTH(datetime_exp) <1>
  249. --------------------------------------------------
  250. *Input*:
  251. <1> date/datetime expression
  252. *Output*: integer
  253. .Description:
  254. Extract the day of the month from a date/datetime.
  255. [source, sql]
  256. --------------------------------------------------
  257. include-tagged::{sql-specs}/docs/docs.csv-spec[dayOfMonth]
  258. --------------------------------------------------
  259. [[sql-functions-datetime-dow]]
  260. ==== `DAY_OF_WEEK/DAYOFWEEK/DOW`
  261. .Synopsis:
  262. [source, sql]
  263. --------------------------------------------------
  264. DAY_OF_WEEK(datetime_exp) <1>
  265. --------------------------------------------------
  266. *Input*:
  267. <1> date/datetime expression
  268. *Output*: integer
  269. .Description:
  270. Extract the day of the week from a date/datetime. Sunday is `1`, Monday is `2`, etc.
  271. [source, sql]
  272. --------------------------------------------------
  273. include-tagged::{sql-specs}/docs/docs.csv-spec[dayOfWeek]
  274. --------------------------------------------------
  275. [[sql-functions-datetime-doy]]
  276. ==== `DAY_OF_YEAR/DOY`
  277. .Synopsis:
  278. [source, sql]
  279. --------------------------------------------------
  280. DAY_OF_YEAR(datetime_exp) <1>
  281. --------------------------------------------------
  282. *Input*:
  283. <1> date/datetime expression
  284. *Output*: integer
  285. .Description:
  286. Extract the day of the year from a date/datetime.
  287. [source, sql]
  288. --------------------------------------------------
  289. include-tagged::{sql-specs}/docs/docs.csv-spec[dayOfYear]
  290. --------------------------------------------------
  291. [[sql-functions-datetime-dayname]]
  292. ==== `DAY_NAME/DAYNAME`
  293. .Synopsis:
  294. [source, sql]
  295. --------------------------------------------------
  296. DAY_NAME(datetime_exp) <1>
  297. --------------------------------------------------
  298. *Input*:
  299. <1> date/datetime expression
  300. *Output*: string
  301. .Description:
  302. Extract the day of the week from a date/datetime in text format (`Monday`, `Tuesday`...).
  303. [source, sql]
  304. --------------------------------------------------
  305. include-tagged::{sql-specs}/docs/docs.csv-spec[dayName]
  306. --------------------------------------------------
  307. [[sql-functions-datetime-hour]]
  308. ==== `HOUR_OF_DAY/HOUR`
  309. .Synopsis:
  310. [source, sql]
  311. --------------------------------------------------
  312. HOUR_OF_DAY(datetime_exp) <1>
  313. --------------------------------------------------
  314. *Input*:
  315. <1> date/datetime expression
  316. *Output*: integer
  317. .Description:
  318. Extract the hour of the day from a date/datetime.
  319. [source, sql]
  320. --------------------------------------------------
  321. include-tagged::{sql-specs}/docs/docs.csv-spec[hourOfDay]
  322. --------------------------------------------------
  323. [[sql-functions-datetime-isodow]]
  324. ==== `ISO_DAY_OF_WEEK/ISODAYOFWEEK/ISODOW/IDOW`
  325. .Synopsis:
  326. [source, sql]
  327. --------------------------------------------------
  328. ISO_DAY_OF_WEEK(datetime_exp) <1>
  329. --------------------------------------------------
  330. *Input*:
  331. <1> date/datetime expression
  332. *Output*: integer
  333. .Description:
  334. Extract the day of the week from a date/datetime, following the https://en.wikipedia.org/wiki/ISO_week_date[ISO 8601 standard].
  335. Monday is `1`, Tuesday is `2`, etc.
  336. [source, sql]
  337. --------------------------------------------------
  338. include-tagged::{sql-specs}/docs/docs.csv-spec[isoDayOfWeek]
  339. --------------------------------------------------
  340. [[sql-functions-datetime-isoweek]]
  341. ==== `ISO_WEEK_OF_YEAR/ISOWEEKOFYEAR/ISOWEEK/IWOY/IW`
  342. .Synopsis:
  343. [source, sql]
  344. --------------------------------------------------
  345. ISO_WEEK_OF_YEAR(datetime_exp) <1>
  346. --------------------------------------------------
  347. *Input*:
  348. <1> date/datetime expression
  349. *Output*: integer
  350. .Description:
  351. Extract the week of the year from a date/datetime, following https://en.wikipedia.org/wiki/ISO_week_date[ISO 8601 standard]. The first week
  352. of a year is the first week with a majority (4 or more) of its days in January.
  353. [source, sql]
  354. --------------------------------------------------
  355. include-tagged::{sql-specs}/docs/docs.csv-spec[isoWeekOfYear]
  356. --------------------------------------------------
  357. [[sql-functions-datetime-minuteofday]]
  358. ==== `MINUTE_OF_DAY`
  359. .Synopsis:
  360. [source, sql]
  361. --------------------------------------------------
  362. MINUTE_OF_DAY(datetime_exp) <1>
  363. --------------------------------------------------
  364. *Input*:
  365. <1> date/datetime expression
  366. *Output*: integer
  367. .Description:
  368. Extract the minute of the day from a date/datetime.
  369. [source, sql]
  370. --------------------------------------------------
  371. include-tagged::{sql-specs}/docs/docs.csv-spec[minuteOfDay]
  372. --------------------------------------------------
  373. [[sql-functions-datetime-minute]]
  374. ==== `MINUTE_OF_HOUR/MINUTE`
  375. .Synopsis:
  376. [source, sql]
  377. --------------------------------------------------
  378. MINUTE_OF_HOUR(datetime_exp) <1>
  379. --------------------------------------------------
  380. *Input*:
  381. <1> date/datetime expression
  382. *Output*: integer
  383. .Description:
  384. Extract the minute of the hour from a date/datetime.
  385. [source, sql]
  386. --------------------------------------------------
  387. include-tagged::{sql-specs}/docs/docs.csv-spec[minuteOfHour]
  388. --------------------------------------------------
  389. [[sql-functions-datetime-month]]
  390. ==== `MONTH_OF_YEAR/MONTH`
  391. .Synopsis:
  392. [source, sql]
  393. --------------------------------------------------
  394. MONTH(datetime_exp) <1>
  395. --------------------------------------------------
  396. *Input*:
  397. <1> date/datetime expression
  398. *Output*: integer
  399. .Description:
  400. Extract the month of the year from a date/datetime.
  401. [source, sql]
  402. --------------------------------------------------
  403. include-tagged::{sql-specs}/docs/docs.csv-spec[monthOfYear]
  404. --------------------------------------------------
  405. [[sql-functions-datetime-monthname]]
  406. ==== `MONTH_NAME/MONTHNAME`
  407. .Synopsis:
  408. [source, sql]
  409. --------------------------------------------------
  410. MONTH_NAME(datetime_exp) <1>
  411. --------------------------------------------------
  412. *Input*:
  413. <1> date/datetime expression
  414. *Output*: string
  415. .Description:
  416. Extract the month from a date/datetime in text format (`January`, `February`...).
  417. [source, sql]
  418. --------------------------------------------------
  419. include-tagged::{sql-specs}/docs/docs.csv-spec[monthName]
  420. --------------------------------------------------
  421. [[sql-functions-now]]
  422. ==== `NOW`
  423. .Synopsis:
  424. [source, sql]
  425. --------------------------------------------------
  426. NOW()
  427. --------------------------------------------------
  428. *Input*: _none_
  429. *Output*: datetime
  430. .Description:
  431. This function offers the same functionality as <<sql-functions-current-timestamp,CURRENT_TIMESTAMP()>> function: returns
  432. the datetime when the current query reached the server. This method always returns the same value for its every
  433. occurrence within the same query.
  434. [source, sql]
  435. --------------------------------------------------
  436. include-tagged::{sql-specs}/docs/docs.csv-spec[nowFunction]
  437. --------------------------------------------------
  438. Typically, this function (as well as its twin <<sql-functions-current-timestamp,CURRENT_TIMESTAMP())>> function is used
  439. for relative date/time filtering:
  440. [source, sql]
  441. --------------------------------------------------
  442. include-tagged::{sql-specs}/docs/docs.csv-spec[filterNow]
  443. --------------------------------------------------
  444. [[sql-functions-datetime-second]]
  445. ==== `SECOND_OF_MINUTE/SECOND`
  446. .Synopsis:
  447. [source, sql]
  448. --------------------------------------------------
  449. SECOND_OF_MINUTE(datetime_exp) <1>
  450. --------------------------------------------------
  451. *Input*:
  452. <1> date/datetime expression
  453. *Output*: integer
  454. .Description:
  455. Extract the second of the minute from a date/datetime.
  456. [source, sql]
  457. --------------------------------------------------
  458. include-tagged::{sql-specs}/docs/docs.csv-spec[secondOfMinute]
  459. --------------------------------------------------
  460. [[sql-functions-datetime-quarter]]
  461. ==== `QUARTER`
  462. .Synopsis:
  463. [source, sql]
  464. --------------------------------------------------
  465. QUARTER(datetime_exp) <1>
  466. --------------------------------------------------
  467. *Input*:
  468. <1> date/datetime expression
  469. *Output*: integer
  470. .Description:
  471. Extract the year quarter the date/datetime falls in.
  472. [source, sql]
  473. --------------------------------------------------
  474. include-tagged::{sql-specs}/docs/docs.csv-spec[quarter]
  475. --------------------------------------------------
  476. [[sql-functions-today]]
  477. ==== `TODAY`
  478. .Synopsis:
  479. [source, sql]
  480. --------------------------------------------------
  481. TODAY()
  482. --------------------------------------------------
  483. *Input*: _none_
  484. *Output*: date
  485. .Description:
  486. This function offers the same functionality as <<sql-functions-current-date,CURRENT_DATE()>> function: returns
  487. the date when the current query reached the server. This method always returns the same value for its every occurrence
  488. within the same query.
  489. [source, sql]
  490. --------------------------------------------------
  491. include-tagged::{sql-specs}/docs/docs.csv-spec[todayFunction]
  492. --------------------------------------------------
  493. Typically, this function (as well as its twin <<sql-functions-current-timestamp,CURRENT_TIMESTAMP())>> function is used
  494. for relative date filtering:
  495. [source, sql]
  496. --------------------------------------------------
  497. include-tagged::{sql-specs}/docs/docs.csv-spec[filterToday]
  498. --------------------------------------------------
  499. [[sql-functions-datetime-week]]
  500. ==== `WEEK_OF_YEAR/WEEK`
  501. .Synopsis:
  502. [source, sql]
  503. --------------------------------------------------
  504. WEEK_OF_YEAR(datetime_exp) <1>
  505. --------------------------------------------------
  506. *Input*:
  507. <1> date/datetime expression
  508. *Output*: integer
  509. .Description:
  510. Extract the week of the year from a date/datetime.
  511. [source, sql]
  512. --------------------------------------------------
  513. include-tagged::{sql-specs}/docs/docs.csv-spec[weekOfYear]
  514. --------------------------------------------------
  515. [[sql-functions-datetime-year]]
  516. ==== `YEAR`
  517. .Synopsis:
  518. [source, sql]
  519. --------------------------------------------------
  520. YEAR(datetime_exp) <1>
  521. --------------------------------------------------
  522. *Input*:
  523. <1> date/datetime expression
  524. *Output*: integer
  525. .Description:
  526. Extract the year from a date/datetime.
  527. [source, sql]
  528. --------------------------------------------------
  529. include-tagged::{sql-specs}/docs/docs.csv-spec[year]
  530. --------------------------------------------------
  531. [[sql-functions-datetime-extract]]
  532. ==== `EXTRACT`
  533. .Synopsis:
  534. [source, sql]
  535. --------------------------------------------------
  536. EXTRACT(
  537. datetime_function <1>
  538. FROM datetime_exp) <2>
  539. --------------------------------------------------
  540. *Input*:
  541. <1> date/time function name
  542. <2> date/datetime expression
  543. *Output*: integer
  544. .Description:
  545. Extract fields from a date/datetime by specifying the name of a <<sql-functions-datetime,datetime function>>.
  546. The following
  547. [source, sql]
  548. --------------------------------------------------
  549. include-tagged::{sql-specs}/docs/docs.csv-spec[extractDayOfYear]
  550. --------------------------------------------------
  551. is the equivalent to
  552. [source, sql]
  553. --------------------------------------------------
  554. include-tagged::{sql-specs}/docs/docs.csv-spec[dayOfYear]
  555. --------------------------------------------------