SPL: Date, Time and Datetime Handling
SPL offers a wealth of functions for date, time and datetime handling, including calculating the current time/date, extracting a component from a date/time/datetime, combining components to generate a date/time/datetime, format conversion, precision tuning, getting a related date, calculating the time interval, calculating the relative date, equivalence comparison, workday-related calculations, evenly spaced time series handling, etc. These functions can be divided into two categories – basic handling and computations.
1. Basic handling
1.1 Calculating the current time/date
| Task | Code | Return value |
| Return the current time, accurate to millisecond | now() | |
| Return the current date | now@d() | |
| Return the current time | now@t() | |
| Return the current time, accurate to minute | now@m() | |
| Return the current time, accurate to second | now@s() |
1.2 Extracting component
Extract the year, month, date, hour, minute, second and week from date, time or datetime objects or strings.
In the following table, dt is a datetime object, such as datetime("2018-08-15 16:07:58:327","yyyy-MM-dd HH🇲🇲ss:SSS").
| Task | Code | Return value |
| Get the year | year(dt) | 2018 |
| Get the month | month(dt) | 8 |
| Get the year and month | month@y(dt) | 201808 |
| Get the day | day(dt) | 15 |
| Get the day of the week for the current date | day@w(dt) | 4(Sunday is 1, Monday is 2) |
| Get the hour | hour(dt) | 16 |
| Get the minute | minute(dt) | 7 |
| Get the second | second(dt) | 58 |
| Get the millisecond | millisecond(dt) | 327 |
| Get the date part in a datetime | date(dt) | date("2018-08-15") |
| Get the time part in a datetime | time(dt) | time("16:07:58") |
| Get the time part in a datetime , accurate to minute | time@m(dt) | time("16:07:00") |
1.3 Combining components
Generate a date, time or datetime object using a year value, a month value, an hour value, a minute value, a second value, a long integer, or a datetime string.
In the following table, dt is a datetime object, such as datetime("2018-08-15 16:07:58:327","yyyy-MM-dd HH🇲🇲ss:SSS").
| Task | Code | Return value |
| Generate a date using the year, month and day components | date(2018,8,15) | |
| Generate a time using the hour, minute and second components | time(16,7,58) | |
| Generate a datetime using the year, month, day, hour, minute, second components | datetime(2018,8,15,16,7,58) | |
| Generate a datetime using the date and time components | datetime(date("2018-08-15"), time("16:07:58")) |
1.4 Conversion
In the following table, d is a date object, such as date("2018-08-15");
t is a time object, such as time("16:07:58:327","HH🇲🇲ss:SSS");
dt is datetime object, such as datetime("2018-08-15 16:07:58:327","yyyy-MM-dd HH🇲🇲ss:SSS").
| Task | Code | Return value |
| Adjust datetime precision, accurate to day | datetime(dt) | 2018-08-15 00:00:00 |
| Adjust datetime precision, accurate to minute | datetime@m(dt) | 2018-08-15 16:07:00 |
| Adjust datetime precision, accurate to second | datetime@s(dt) | 2018-08-15 16:07:58 |
| Convert a string to date of default format | date("2018-08-15") | |
| Convert a string to date according to the specified format | date("08/15/2018","MM/dd/yyyy") | |
| Convert a string to date according to the specified format | date("201808","yyyyMM") | 2018-08-01 |
| Convert a string to time of default format | time("16:07:58") | |
| Convert a string to time according to the specified format | time("4:07 PM","h:mm a") | 16:07:00 |
| Convert a string to datetime according to the specified format | datetime("2018-08-15 16:07:58") | |
| Convert a string to datetime according to the specified format | datetime("08/15/2018 4:07 PM", "MM/dd/yyyy h:mm a") | |
| Convert to default date format string | string(d) | 2018-08-15 |
| Convert to the specified date format string | string(dt,"MM/dd/yyyy") | 08/15/2018 |
| Convert to the specified date format string | string(dt,"yyyyMM") | 201808 |
| Convert to default time format string | string(t) | 16:07:58 |
| Convert to the specified time format string | string(dt,"h:mm a") | 4:07 PM |
| Convert to default datetime format string | string(dt) | 2018-08-15 16:07:58 |
| Convert to the specified datetime format string | string(dt,"MM/dd/yyyy h:mm a") | 08/15/2018 4:07 PM |
| Convert to a long integer to datetime | datetime(321656865654) | 1980-03-12 05:07:45 |
| Convert to a long integer (represented as the number of milliseconds since January 1, 1970, 00:00:00 GMT) | long(dt) | 1534320478000 |
2. Computations
2.1 Calculating a related date
In the following table, dt is a date object, such as date("2020-04-16").
| Task | Code | Return value |
| Get the first day (Sunday) of the week given in the specified date | pdate@w(dt) | 2020-04-12 |
| Get the first day of the month given in the specified date | pdate@m(dt) | 2020-04-01 |
| Get the first day of the quarter given in the specified date | pdate@q(dt) | 2020-04-01 |
| Get the first day of the year given in the specified date | pdate@y(dt) | 2020-01-01 |
| Get the last day (Saturday) of the week given in the specified date | pdate@we(dt) | 2020-04-18 |
| Get the last day of the month given in the specified date | pdate@me(dt) | 2020-04-30 |
| Get the last day of the quarter given in the specified date | pdate@qe(dt) | 2020-06-30 |
| Get the last day of the year given in the specified date | pdate@ye(dt) | 2020-12-31 |
| Count the days of the month given in the specified date | days(dt) | 30 |
| Count the days of the quarter given in the specified date | days@q(dt) | 91 |
| Count the days of the year given in the specified date | days@y(dt) | 366 |
2.2 Calculating the time interval
Calculate the time interval between two time points.
In the following table, dt1 and dt2 are datetime("2008-08-08 20:00:00") and datetime("2018-05-28 10:27:15") respectively.
| Task | Code | Return value |
| Get the number of full years between the given datetime and the current datetime | age(dt1) | 13 |
| Get the number of full years between two given datetimes | age(dt1,dt2) | 9 |
| Get the number of years between the years that two given datetimes belong to | age@y(dt1,dt2) | 10 |
| Get the number of years between the months that two given datetimes belong to | age@m(dt1,dt2) | 9 |
| Get the difference between two given datetimes in terms of years | interval@y(dt1,dt2) | 10 |
| Get the difference between two given datetimes in terms of quarters | interval@q(dt1,dt2) | 40 |
| Get the difference between two given datetimes in terms of months | interval@m(dt1,dt2) | 117 |
| Get the difference between two given datetimes in terms of seconds | interval@s(dt1,dt2) | 309277635 |
| Get the difference between two given datetimes in terms of milliseconds | interval@ms(dt1,dt2) | 309277635000 |
| Get the number of Sundays between two given datetimes | interval@7(dt1,dt2) | 512 |
| Get the number of Mondays between two given datetimes | interval@1(dt1,dt2) | 512 |
| Get the difference between two given datetimes in terms of days and return an integer | interval(dt1,dt2) | 3580 |
| Get the difference between two given datetimes in terms of days and return an integer | dt2-dt1 | 3580 |
| Get the difference between two given datetimes in terms of days and return a real number | interval@r(dt1,dt2) | 3579.6022569444444 |
2.3 Calculating a relative datetime
elapse(dt,k)
The function calculates a new datetime when a specified number of days, years, quarters, months, seconds or milliseconds k is added to or subtracted from a given datetime dt. The new date is in the future when k is a positive number and in the past when it is a negative number. If the original datetime is in the last day a month, the function, by default, will adjust the result datetime to the last day of the current month.
In the following table, dt is datetime("2017-02-28 10:27:15"), which is in the last day of February.
| Task | Code | Return value |
| Get the datetime three days after the given datetime | dt+3 | 2017-03-03 10:27:15 |
| Get the datetime three days after the given datetime | elapse(A2,3) | 2017-03-03 10:27:15 |
| Get the datetime three years after the given datetime (adjust the result to the last day of the current month) | elapse@y(A2,3) | 2020-02-29 10:27:15 |
| Get the datetime three years after the given datetime (do not adjust the result to the last day of the current month) | elapse@ye(A2,3) | 2020-02-28 10:27:15 |
| Get the datetime three quarters after the given datetime (adjust the result to the last day of the current month) | elapse@q(A2,3) | 2017-11-30 10:27:15 |
| Get the datetime three quarters after the given datetime (do not adjust the result to the last day of the current month) | elapse@qe(A2,3) | 2017-11-28 10:27:15 |
| Get the datetime four months before the given datetime (adjust the result to the last day of the current month) | elapse@m(A2,-4) | 2016-10-31 10:27:15 |
| Get the datetime four months before the given datetime (do not adjust the result to the last day of the current month) | elapse@me(A2,-4) | 2016-10-28 10:27:15 |
| Get the datetime 100 seconds after the given datetime | elapse@s(A2,100) | 2017-02-28 10:28:55 |
| Get the datetime 368 milliseconds after the given datetime | elapse@ms(A2,368) | 2017-02-28 10:27:15:368 |
2.4 Equivalence comparison
| Task | Code | Return value |
| Compare whether two dates are equal | deq("1988-12-08","1988-12-07") | false |
| Compare whether the years in the two given dates are equal | deq@y(date("1988-11-08"),date("1988-09-12")) | true |
| Compare whether the months in the two given dates are equal | deq@m(date("1988-11-08"),date("1988-09-12")) | false |
| Compare whether the quarters in the two given dates are equal | deq@q(date("1988-12-08"),date("1988-10-12")) | true |
| Compare whether the ten-day periods in the two given dates are equal | deq@t(date("1988-10-08"),date("1988-10-12")) | false |
| Compare whether the weeks in the two given dates are equal | deq@w(date("1988-10-05"),date("1988-10-08")) | true |
2.5 Workday-related calculations
workday(dt,k,h) Calculates the workday k days after the given date dt.
workdays(dt1,dt2,h) Returns a sequence of workdays between date dt1 and date dt2.
h is a sequence of holidays or non-holidays, whose member (members) is (are) treated as a holiday (holidays) if they are not weekends, and as a non-holiday (non-holidays) if they are weekends.
| Task | Code | Return value |
| Get the workday two workdays of the given date | workday(date("2020-04-29"),2,[date("2020-05-01")]) | 2020-05-04 |
| 2020-05-01is Friday, which is a public holiday | ||
| Get the workday two workdays of the given date | workday(date("2020-09-25"),2,[date("2020-09-27")]) | 2020-09-28 |
| 2020-09-27 is a Sunday, which is a workday in lieu | ||
| Get a sequence of workdays between two given dates | =workdays(date("2020-09-24"),date("2020-09-29"),[date("2020-09-27")]) | [2020-09-24,2020-09-25,2020-09-27,2020-09-28,2020-09-29] |
2.6 Evenly-spaced time series
periods(s,e,i)
s and e are time variables; i is an integer. The function returns a sequence of dates/times that are a certain time period (i) apart between two given time points s (inclusive) and e (inclusive). The default unit of i is day, and its default value is 1. We can use @x option in the function to exclude the endpoints.
| Task | Code | Return value |
| Get a sequence of dates that are three days apart between two given time points | periods("2018-09-25","2018-10-06",3) | [2018-09-25,2018-09-28, 2018-10-01,2018-10-04, 2018-10-06] |
| Get a sequence of dates that are three years apart between two given time points(Adjust to full years apart) | periods@y("2010-09-25","2018-10-06",3) | [2010-09-25,2013-01-01, 2016-01-01,2018-10-06] |
| Get a sequence of dates that are three years apart between two given time points (Do not adjust to full years apart) | periods@yo("2010-09-25","2018-10-06",3) | [2010-09-25,2013-09-25, 2016-09-25,2018-10-06] |
| Get a sequence of dates that are three quarters apart between two given time points(Adjust to full quarters apart) | periods@q("2010-09-25","2012-05-06",3) | [2010-09-25,2011-04-01, 2012-01-01,2012-05-06] |
| Get a sequence of dates that are three quarters apart between two given time points(Do not adjust to full quarters apart) | periods@qo("2010-09-25","2012-05-06",3) | [2010-09-25,2011-06-25, 2012-03-25,2012-05-06] |
| Get a sequence of dates that are three months apart between two given time points(Adjust to full months apart) | periods@m("2010-09-25","2011-04-06",3) | [2010-09-25,2010-12-01, 2011-03-01,2011-04-06] |
| Get a sequence of dates that are three months apart between two given time points(Do not adjust to full months apart) | periods@mo("2010-09-25","2011-04-06",3) | [2010-09-25,2010-12-25, 2011-03-25,2011-04-06] |
| Get a sequence of dates that are two ten-day periods apart between two given time points | periods@t("2010-09-25","2010-11-06",2) | [2010-09-25,2010-10-11, 2010-11-01,2010-11-06] |
| Get a sequence of times that are three seconds apart between two given time points | periods@s("08:25:30","08:25:39",3) | [08:25:30,08:25:33, 08:25:36,08:25:39] |
range(s,e,k:n)
Both s and e are time variables. The function divides the time interval between s and e evenly into n segments, and returns the time range of the kth segment. The result’s degree of precision is determined by the data type of s and e. Accurate to day when they are date type, and to second when they are datetime type.
| Task | Code | Return value |
| Get the segment points after the interval between two given dates are trisected | range(date("2018-08-09"),date("2020-02-20"),3) | [2018-08-09,2019-02-12, 2019-08-18,2020-02-20] |
| Get the range of the 2nd segment after the interval between two given dates are trisected | range(date("2018-08-09"),date("2020-02-20"),2:3) | [2019-02-12,2019-08-18] |
| Get the range of the 2nd segment after the interval between two given datetimes are trisected | range(datetime("2018-01-01 10:20:30"),datetime("2020-01-01 10:00:00"),2:3) | [2018-09-01 18:13:40, 2019-05-03 02:06:50] |
Find more date/time/datetime operations in date(), time(), and datetime() functions in SPL Function Reference.
