RaqForum 22 No.
1 Reply  •  519 View  • 
     ...

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.

 
 •  519 View  •