SPL: Date, Time and Datetime Handling

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

TaskCodeReturn value
Return the current time, accurate to millisecondnow()
Return the current datenow@d()
Return the current timenow@t()
Return the current time, accurate to minutenow@m()
Return the current time, accurate to secondnow@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").

TaskCodeReturn value
Get the yearyear(dt)2018
Get the monthmonth(dt)8
Get the year and monthmonth@y(dt)201808
Get the dayday(dt)15
Get the day of the week for the current dateday@w(dt)4(Sunday is 1, Monday is 2)
Get the hourhour(dt)16
Get the minuteminute(dt)7
Get the secondsecond(dt)58
Get the millisecondmillisecond(dt)327
Get the date part in a datetimedate(dt)date("2018-08-15")
Get the time part in a datetimetime(dt)time("16:07:58")
Get the time part in a datetime , accurate to minutetime@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").

TaskCodeReturn value
Generate a date using the year, month and day componentsdate(2018,8,15)
Generate a time using the hour, minute and second componentstime(16,7,58)
Generate a datetime using the year, month, day, hour, minute, second componentsdatetime(2018,8,15,16,7,58)
Generate a datetime using the date and time componentsdatetime(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").

TaskCodeReturn value
Adjust datetime precision, accurate to daydatetime(dt)2018-08-15 00:00:00
Adjust datetime precision, accurate to minutedatetime@m(dt)2018-08-15 16:07:00
Adjust datetime precision, accurate to seconddatetime@s(dt)2018-08-15 16:07:58
Convert a string to date of default formatdate("2018-08-15")
Convert a string to date according to the specified formatdate("08/15/2018","MM/dd/yyyy")
Convert a string to date according to the specified formatdate("201808","yyyyMM")2018-08-01
Convert a string to time of default formattime("16:07:58")
Convert a string to time according to the specified formattime("4:07 PM","h:mm a")16:07:00
Convert a string to datetime according to the specified formatdatetime("2018-08-15 16:07:58")
Convert a string to datetime according to the specified formatdatetime("08/15/2018 4:07 PM", "MM/dd/yyyy h:mm a")
Convert to default date format stringstring(d)2018-08-15
Convert to the specified date format stringstring(dt,"MM/dd/yyyy")08/15/2018
Convert to the specified date format stringstring(dt,"yyyyMM")201808
Convert to default time format stringstring(t)16:07:58
Convert to the specified time format stringstring(dt,"h:mm a")4:07 PM
Convert to default datetime format stringstring(dt)2018-08-15 16:07:58
Convert to the specified datetime format stringstring(dt,"MM/dd/yyyy h:mm a")08/15/2018 4:07 PM
Convert to a long integer to datetimedatetime(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").

TaskCodeReturn value
Get the first day (Sunday) of the week given in the specified datepdate@w(dt)2020-04-12
Get the first day of the month given in the specified datepdate@m(dt)2020-04-01
Get the first day of the quarter given in the specified datepdate@q(dt)2020-04-01
Get the first day of the year given in the specified datepdate@y(dt)2020-01-01
Get the last day (Saturday) of the week given in the specified datepdate@we(dt)2020-04-18
Get the last day of the month given in the specified datepdate@me(dt)2020-04-30
Get the last day of the quarter given in the specified datepdate@qe(dt)2020-06-30
Get the last day of the year given in the specified datepdate@ye(dt)2020-12-31
Count the days of the month given in the specified datedays(dt)30
Count the days of the quarter given in the specified datedays@q(dt)91
Count the days of the year given in the specified datedays@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.

TaskCodeReturn value
Get the number of full years between the given datetime and the current datetimeage(dt1)13
Get the number of full years between two given datetimesage(dt1,dt2)9
Get the number of years between the years that two given datetimes belong toage@y(dt1,dt2)10
Get the number of years between the months that two given datetimes belong toage@m(dt1,dt2)9
Get the difference between two given datetimes in terms of yearsinterval@y(dt1,dt2)10
Get the difference between two given datetimes in terms of quartersinterval@q(dt1,dt2)40
Get the difference between two given datetimes in terms of monthsinterval@m(dt1,dt2)117
Get the difference between two given datetimes in terms of secondsinterval@s(dt1,dt2)309277635
Get the difference between two given datetimes in terms of millisecondsinterval@ms(dt1,dt2)309277635000
Get the number of Sundays between two given datetimesinterval@7(dt1,dt2)512
Get the number of Mondays between two given datetimesinterval@1(dt1,dt2)512
Get the difference between two given datetimes in terms of days and return an integerinterval(dt1,dt2)3580
Get the difference between two given datetimes in terms of days and return an integerdt2-dt13580
Get the difference between two given datetimes in terms of days and return a real numberinterval@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.

TaskCodeReturn value
Get the datetime three days after the given datetimedt+32017-03-03 10:27:15
Get the datetime three days after the given datetimeelapse(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 datetimeelapse@s(A2,100)2017-02-28 10:28:55
Get the datetime 368 milliseconds after the given datetimeelapse@ms(A2,368)2017-02-28 10:27:15:368

2.4 Equivalence comparison

TaskCodeReturn value
Compare whether two dates are equaldeq("1988-12-08","1988-12-07")false
Compare whether the years in the two given dates are equaldeq@y(date("1988-11-08"),date("1988-09-12"))true
Compare whether the months in the two given dates are equaldeq@m(date("1988-11-08"),date("1988-09-12"))false
Compare whether the quarters in the two given dates are equaldeq@q(date("1988-12-08"),date("1988-10-12"))true
Compare whether the ten-day periods in the two given dates are equaldeq@t(date("1988-10-08"),date("1988-10-12"))false
Compare whether the weeks in the two given dates are equaldeq@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.

TaskCodeReturn value
Get the workday two workdays of the given dateworkday(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 dateworkday(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.

TaskCodeReturn value
Get a sequence of dates that are three days apart between two given time pointsperiods("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 pointsperiods@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 pointsperiods@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.

TaskCodeReturn value
Get the segment points after the interval between two given dates are trisectedrange(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 trisectedrange(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 trisectedrange(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.

Leave a Reply