Skip to content

Commit 8c8e08c

Browse files
matthewryanwellsmatthewryanwells
authored
Added new datetime functions and aliases to PPL [Part 2] (#283) (#1852)
* Added new datetime functions, documentation, and tests Signed-off-by: Matthew Wells <matthew.wells@improving.com>
1 parent dae5a85 commit 8c8e08c

7 files changed

Lines changed: 615 additions & 4 deletions

File tree

‎docs/user/ppl/functions/datetime.rst‎

Lines changed: 288 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -813,6 +813,77 @@ Example::
813813
+-----------------------------------+
814814

815815

816+
EXTRACT
817+
-------
818+
819+
Description
820+
>>>>>>>>>>>
821+
822+
Usage: extract(part FROM date) returns a LONG with digits in order according to the given 'part' arguments.
823+
The specific format of the returned long is determined by the table below.
824+
825+
Argument type: PART, where PART is one of the following tokens in the table below.
826+
827+
The format specifiers found in this table are the same as those found in the `DATE_FORMAT`_ function.
828+
829+
.. list-table:: The following table describes the mapping of a 'part' to a particular format.
830+
:widths: 20 80
831+
:header-rows: 1
832+
833+
* - Part
834+
- Format
835+
* - MICROSECOND
836+
- %f
837+
* - SECOND
838+
- %s
839+
* - MINUTE
840+
- %i
841+
* - HOUR
842+
- %H
843+
* - DAY
844+
- %d
845+
* - WEEK
846+
- %X
847+
* - MONTH
848+
- %m
849+
* - YEAR
850+
- %V
851+
* - SECOND_MICROSECOND
852+
- %s%f
853+
* - MINUTE_MICROSECOND
854+
- %i%s%f
855+
* - MINUTE_SECOND
856+
- %i%s
857+
* - HOUR_MICROSECOND
858+
- %H%i%s%f
859+
* - HOUR_SECOND
860+
- %H%i%s
861+
* - HOUR_MINUTE
862+
- %H%i
863+
* - DAY_MICROSECOND
864+
- %d%H%i%s%f
865+
* - DAY_SECOND
866+
- %d%H%i%s
867+
* - DAY_MINUTE
868+
- %d%H%i
869+
* - DAY_HOUR
870+
- %d%H%
871+
* - YEAR_MONTH
872+
- %V%m
873+
874+
Return type: LONG
875+
876+
Example::
877+
878+
os> source=people | eval `extract(YEAR_MONTH FROM "2023-02-07 10:11:12")` = extract(YEAR_MONTH FROM "2023-02-07 10:11:12") | fields `extract(YEAR_MONTH FROM "2023-02-07 10:11:12")`
879+
fetched rows / total rows = 1/1
880+
+--------------------------------------------------+
881+
| extract(YEAR_MONTH FROM "2023-02-07 10:11:12") |
882+
|--------------------------------------------------|
883+
| 202302 |
884+
+--------------------------------------------------+
885+
886+
816887
FROM_DAYS
817888
---------
818889

@@ -872,6 +943,28 @@ Examples::
872943
+-----------------------------------+
873944

874945

946+
GET_FORMAT
947+
----------
948+
949+
Description
950+
>>>>>>>>>>>
951+
952+
Usage: Returns a string value containing string format specifiers based on the input arguments.
953+
954+
Argument type: TYPE, STRING, where TYPE must be one of the following tokens: [DATE, TIME, DATETIME, TIMESTAMP], and
955+
STRING must be one of the following tokens: ["USA", "JIS", "ISO", "EUR", "INTERNAL"] (" can be replaced by ').
956+
957+
Examples::
958+
959+
os> source=people | eval `GET_FORMAT(DATE, 'USA')` = GET_FORMAT(DATE, 'USA') | fields `GET_FORMAT(DATE, 'USA')`
960+
fetched rows / total rows = 1/1
961+
+---------------------------+
962+
| GET_FORMAT(DATE, 'USA') |
963+
|---------------------------|
964+
| %m.%d.%Y |
965+
+---------------------------+
966+
967+
875968
HOUR
876969
----
877970

@@ -922,6 +1015,26 @@ Example::
9221015
+---------------------------------+
9231016

9241017

1018+
LAST_DAY
1019+
--------
1020+
1021+
Usage: Returns the last day of the month as a DATE for a valid argument.
1022+
1023+
Argument type: DATE/DATETIME/STRING/TIMESTAMP/TIME
1024+
1025+
Return type: DATE
1026+
1027+
Example::
1028+
1029+
os> source=people | eval `last_day('2023-02-06')` = last_day('2023-02-06') | fields `last_day('2023-02-06')`
1030+
fetched rows / total rows = 1/1
1031+
+--------------------------+
1032+
| last_day('2023-02-06') |
1033+
|--------------------------|
1034+
| 2023-02-28 |
1035+
+--------------------------+
1036+
1037+
9251038
LOCALTIMESTAMP
9261039
--------------
9271040

@@ -1136,7 +1249,6 @@ Return type: INTEGER
11361249

11371250
Synonyms: `MONTH_OF_YEAR`_
11381251

1139-
11401252
Example::
11411253

11421254
os> source=people | eval `MONTH(DATE('2020-08-26'))` = MONTH(DATE('2020-08-26')) | fields `MONTH(DATE('2020-08-26'))`
@@ -1289,6 +1401,32 @@ Example::
12891401
+-------------------------------+
12901402

12911403

1404+
SEC_TO_TIME
1405+
-----------
1406+
1407+
Description
1408+
>>>>>>>>>>>
1409+
1410+
Usage: sec_to_time(number) returns the time in HH:mm:ssss[.nnnnnn] format.
1411+
Note that the function returns a time between 00:00:00 and 23:59:59.
1412+
If an input value is too large (greater than 86399), the function will wrap around and begin returning outputs starting from 00:00:00.
1413+
If an input value is too small (less than 0), the function will wrap around and begin returning outputs counting down from 23:59:59.
1414+
1415+
Argument type: INTEGER, LONG, DOUBLE, FLOAT
1416+
1417+
Return type: TIME
1418+
1419+
Example::
1420+
1421+
os> source=people | eval `SEC_TO_TIME(3601)` = SEC_TO_TIME(3601) | eval `SEC_TO_TIME(1234.123)` = SEC_TO_TIME(1234.123) | fields `SEC_TO_TIME(3601)`, `SEC_TO_TIME(1234.123)`
1422+
fetched rows / total rows = 1/1
1423+
+---------------------+-------------------------+
1424+
| SEC_TO_TIME(3601) | SEC_TO_TIME(1234.123) |
1425+
|---------------------+-------------------------|
1426+
| 01:00:01 | 00:20:34.123 |
1427+
+---------------------+-------------------------+
1428+
1429+
12921430
SECOND
12931431
------
12941432

@@ -1339,6 +1477,32 @@ Example::
13391477
+--------------------------------------+
13401478

13411479

1480+
STR_TO_DATE
1481+
-----------
1482+
1483+
Description
1484+
>>>>>>>>>>>
1485+
1486+
Usage: str_to_date(string, string) is used to extract a DATETIME from the first argument string using the formats specified in the second argument string.
1487+
The input argument must have enough information to be parsed as a DATE, DATETIME, or TIME.
1488+
Acceptable string format specifiers are the same as those used in the `DATE_FORMAT`_ function.
1489+
It returns NULL when a statement cannot be parsed due to an invalid pair of arguments, and when 0 is provided for any DATE field. Otherwise, it will return a DATETIME with the parsed values (as well as default values for any field that was not parsed).
1490+
1491+
Argument type: STRING, STRING
1492+
1493+
Return type: DATETIME
1494+
1495+
Example::
1496+
1497+
OS> source=people | eval `str_to_date("01,5,2013", "%d,%m,%Y")` = str_to_date("01,5,2013", "%d,%m,%Y") | fields = `str_to_date("01,5,2013", "%d,%m,%Y")`
1498+
fetched rows / total rows = 1/1
1499+
+----------------------------------------+
1500+
| str_to_date("01,5,2013", "%d,%m,%Y") |
1501+
|----------------------------------------|
1502+
| 2013-05-01 00:00:00 |
1503+
+----------------------------------------+
1504+
1505+
13421506
SUBDATE
13431507
-------
13441508

@@ -1637,6 +1801,57 @@ Example::
16371801
+------------------------------------+------------------------------------------------------+
16381802

16391803

1804+
TIMESTAMPADD
1805+
------------
1806+
1807+
Description
1808+
>>>>>>>>>>>
1809+
1810+
Usage: Returns a DATETIME value based on a passed in DATE/DATETIME/TIME/TIMESTAMP/STRING argument and an INTERVAL and INTEGER argument which determine the amount of time to be added.
1811+
If the third argument is a STRING, it must be formatted as a valid DATETIME. If only a TIME is provided, a DATETIME is still returned with the DATE portion filled in using the current date.
1812+
If the third argument is a DATE, it will be automatically converted to a DATETIME.
1813+
1814+
Argument type: INTERVAL, INTEGER, DATE/DATETIME/TIME/TIMESTAMP/STRING
1815+
1816+
INTERVAL must be one of the following tokens: [MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR]
1817+
1818+
Examples::
1819+
1820+
os> source=people | eval `TIMESTAMPADD(DAY, 17, '2000-01-01 00:00:00')` = TIMESTAMPADD(DAY, 17, '2000-01-01 00:00:00') | eval `TIMESTAMPADD(QUARTER, -1, '2000-01-01 00:00:00')` = TIMESTAMPADD(QUARTER, -1, '2000-01-01 00:00:00') | fields `TIMESTAMPADD(DAY, 17, '2000-01-01 00:00:00')`, `TIMESTAMPADD(QUARTER, -1, '2000-01-01 00:00:00')`
1821+
fetched rows / total rows = 1/1
1822+
+------------------------------------------------+----------------------------------------------------+
1823+
| TIMESTAMPADD(DAY, 17, '2000-01-01 00:00:00') | TIMESTAMPADD(QUARTER, -1, '2000-01-01 00:00:00') |
1824+
|------------------------------------------------+----------------------------------------------------|
1825+
| 2000-01-18 00:00:00 | 1999-10-01 00:00:00 |
1826+
+------------------------------------------------+----------------------------------------------------+
1827+
1828+
1829+
TIMESTAMPDIFF
1830+
-------------
1831+
1832+
Description
1833+
>>>>>>>>>>>
1834+
1835+
Usage: TIMESTAMPDIFF(interval, start, end) returns the difference between the start and end date/times in interval units.
1836+
If a TIME is provided as an argument, it will be converted to a DATETIME with the DATE portion filled in using the current date.
1837+
Arguments will be automatically converted to a DATETIME/TIME/TIMESTAMP when appropriate.
1838+
Any argument that is a STRING must be formatted as a valid DATETIME.
1839+
1840+
Argument type: INTERVAL, DATE/DATETIME/TIME/TIMESTAMP/STRING, DATE/DATETIME/TIME/TIMESTAMP/STRING
1841+
1842+
INTERVAL must be one of the following tokens: [MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR]
1843+
1844+
Examples::
1845+
1846+
os> source=people | eval `TIMESTAMPDIFF(YEAR, '1997-01-01 00:00:00', '2001-03-06 00:00:00')` = TIMESTAMPDIFF(YEAR, '1997-01-01 00:00:00', '2001-03-06 00:00:00') | eval `TIMESTAMPDIFF(SECOND, time('00:00:23'), time('00:00:00'))` = TIMESTAMPDIFF(SECOND, time('00:00:23'), time('00:00:00')) | fields `TIMESTAMPDIFF(YEAR, '1997-01-01 00:00:00', '2001-03-06 00:00:00')`, `TIMESTAMPDIFF(SECOND, time('00:00:23'), time('00:00:00'))`
1847+
fetched rows / total rows = 1/1
1848+
+---------------------------------------------------------------------+-------------------------------------------------------------+
1849+
| TIMESTAMPDIFF(YEAR, '1997-01-01 00:00:00', '2001-03-06 00:00:00') | TIMESTAMPDIFF(SECOND, time('00:00:23'), time('00:00:00')) |
1850+
|---------------------------------------------------------------------+-------------------------------------------------------------|
1851+
| 4 | -23 |
1852+
+---------------------------------------------------------------------+-------------------------------------------------------------+
1853+
1854+
16401855
TO_DAYS
16411856
-------
16421857

@@ -1660,6 +1875,30 @@ Example::
16601875
+-------------------------------+
16611876

16621877

1878+
TO_SECONDS
1879+
----------
1880+
1881+
Description
1882+
>>>>>>>>>>>
1883+
1884+
Usage: to_seconds(date) returns the number of seconds since the year 0 of the given value. Returns NULL if value is invalid.
1885+
An argument of a LONG type can be used. It must be formatted as YMMDD, YYMMDD, YYYMMDD or YYYYMMDD. Note that a LONG type argument cannot have leading 0s as it will be parsed using an octal numbering system.
1886+
1887+
Argument type: STRING/LONG/DATE/DATETIME/TIME/TIMESTAMP
1888+
1889+
Return type: LONG
1890+
1891+
Example::
1892+
1893+
os> source=people | eval `TO_SECONDS(DATE('2008-10-07'))` = TO_SECONDS(DATE('2008-10-07')) | eval `TO_SECONDS(950228)` = TO_SECONDS(950228) | fields `TO_SECONDS(DATE('2008-10-07'))`, `TO_SECONDS(950228)`
1894+
fetched rows / total rows = 1/1
1895+
+----------------------------------+----------------------+
1896+
| TO_SECONDS(DATE('2008-10-07')) | TO_SECONDS(950228) |
1897+
|----------------------------------+----------------------|
1898+
| 63390556800 | 62961148800 |
1899+
+----------------------------------+----------------------+
1900+
1901+
16631902
UNIX_TIMESTAMP
16641903
--------------
16651904

@@ -1821,6 +2060,31 @@ Example::
18212060
+----------------------------+-------------------------------+
18222061

18232062

2063+
WEEKDAY
2064+
-------
2065+
2066+
Description
2067+
>>>>>>>>>>>
2068+
2069+
Usage: weekday(date) returns the weekday index for date (0 = Monday, 1 = Tuesday, ..., 6 = Sunday).
2070+
2071+
It is similar to the `dayofweek`_ function, but returns different indexes for each day.
2072+
2073+
Argument type: STRING/DATE/DATETIME/TIME/TIMESTAMP
2074+
2075+
Return type: INTEGER
2076+
2077+
Example::
2078+
2079+
os> source=people | eval `weekday(DATE('2020-08-26'))` = weekday(DATE('2020-08-26')) | eval `weekday(DATE('2020-08-27'))` = weekday(DATE('2020-08-27')) | fields `weekday(DATE('2020-08-26'))`, `weekday(DATE('2020-08-27'))`
2080+
fetched rows / total rows = 1/1
2081+
+-------------------------------+-------------------------------+
2082+
| weekday(DATE('2020-08-26')) | weekday(DATE('2020-08-27')) |
2083+
|-------------------------------+-------------------------------|
2084+
| 2 | 3 |
2085+
+-------------------------------+-------------------------------+
2086+
2087+
18242088
WEEK_OF_YEAR
18252089
------------
18262090

@@ -1910,3 +2174,26 @@ Example::
19102174
+----------------------------+
19112175

19122176

2177+
YEARWEEK
2178+
--------
2179+
2180+
Description
2181+
>>>>>>>>>>>
2182+
2183+
Usage: yearweek(date) returns the year and week for date as an integer. It accepts and optional mode arguments aligned with those available for the `WEEK`_ function.
2184+
2185+
Argument type: STRING/DATE/DATETIME/TIME/TIMESTAMP
2186+
2187+
Return type: INTEGER
2188+
2189+
Example::
2190+
2191+
os> source=people | eval `YEARWEEK('2020-08-26')` = YEARWEEK('2020-08-26') | eval `YEARWEEK('2019-01-05', 1)` = YEARWEEK('2019-01-05', 1) | fields `YEARWEEK('2020-08-26')`, `YEARWEEK('2019-01-05', 1)`
2192+
fetched rows / total rows = 1/1
2193+
+--------------------------+-----------------------------+
2194+
| YEARWEEK('2020-08-26') | YEARWEEK('2019-01-05', 1) |
2195+
|--------------------------+-----------------------------|
2196+
| 202034 | 201901 |
2197+
+--------------------------+-----------------------------+
2198+
2199+

0 commit comments

Comments
 (0)