In data analysis and calculation, date time data is a special type. Here we will study how to use date time data in SPL.

1 Conversion and generation of date time data

Date time data, usually entered or displayed as a string. When using esProc, you can click tool > option to set the default format of date time type data in the environment page of option configuration, such as:

When using SPL, data of date time type will be displayed in the default format, such as:

	A
1	=now()

After running, check the result in A1 as follows:

 

The function now () used here is a commonly used function in date and time calculation, which can obtain the current date and time in the system. The display of date and time is different in different language environments, especially the data such as month and week. The following will be explained with the format of English version as an example.

When you need to enter a constant of date time type, you can also directly enter a string according to the format. At this time, SPL will automatically parse the data into date time data, such as:

	A	B	C
1	2019-2-1	12:45:30	2019-02-01 10:30:00

A1, B1 and C1 will be resolved to date type, date time type and time type data respectively, as follows:

 

SPL can resolve the directly entered constant to date time type, but if it is already string type, you need to use date(), time() or datetime() to convert the string to date, time or date time data, such as:

	A	B	C
1	2019	2	20
2	=A1/"-"/B1/"-"/C1	=12/":"/22/":00"	=A2+" "+B2
3	=ifdate(A2)	=iftime(B2)	=ifdate(C2)
4	=date(A2)	=time(B2)	=datetime(C2)
5	=ifdate(A4)	=iftime(B4)	=ifdate(C4)

A2, B2 and C2 are strings obtained by string operation:

Line 3 uses ifdate()and iftime() functions to judge whether the data in line 2 has been processed as date or time type (Note: ifdate() is used to judge the data of date type and date time type). The results in A3, B3 and C3 are as follows:

 

It can be seen from the results that the cell values in Row 2 are not date or time data, but are actually strings, and the display is also different from date and time.            

In line 4, convert the string to date time data according to the corresponding format, and the result is as follows:

 

In line 5, judge whether the data in line 4 has been processed as date or time type, and the result is as follows:

 

 When using external data, sometimes you need to process date and time data in different formats. In this case, you can add a display format string after the string when using date(), time() or datetime(), such as:

	A	B	C
1	Feb 2, 2019	'2:30:45 PM	'2019-6-20 2:30:45 PM
2	MMM d,yyyy	hⓂs a	yyyy-M-d hⓂs a
3	=date(A1,A2)	=time(B1,B2)	=datetime(C1,C2)

The data in line 1 is not in the default date time format, and the 'character is added in front of B1 and C1 to indicate the use of string constants. The data in A1, B1 and C1 are as follows:

It should be noted that the setting of MMM format is related to the language environment. In the English environment, it means the abbreviation of month, such as Feb; in the Chinese environment, it will be displayed as Chinese month.            

The format strings listed in line 2 indicate how the SPL parses the data in line 1, and line 3 uses these format strings for conversion, with the following results:

 

After the type conversion, the results are still displayed in the default format when viewed. If you want to display in other formats, you can use the string (D, FMT) function to convert the date time class data to a string of the specified format, such as:

	A	B	C
1	2019-02-21	12:45:30	2019-12-01 10:30:00
2	MMMM d,yyyy	hⓂs a	MMM d,yyyy hⓂs a
3	=string(A1,A2)	=string(B1,B2)	=string(C1,C2)

In A3, B3 and C3, convert date time data to a string of specified format:

 

Of course, you can also directly modify the default date time display format as required. 

When using date(), time() or datetime() to generate data types, you can also directly specify the year, month, day, hour, minute, second and other components in turn:

	A	B	C
1	=date(2019,2,21)	=time(13,5,0)	=datetime(2019,12,29,13,5,0)

The result is as follows:

 

When specifying each component, pay attention to the reasonable range of each integer, for example, the hourly component is between 0 and 23.

2 Display format of date time data

	A	B	C
1	=date(201906,29)	=time(13:5:18)	=datetime(A1,B1)

In this example, date () is used to generate date in A1. The first parameter uses 6 digits, and SPL will use it to represent year and month at the same time. C1 combines the date in A1 with the time in B1 to get the date time data, and the result is as follows:

 

In the previous section, some display formats of date and time data have been used. Format strings are used to specify display styles, such as yyyy for 4-digit year, DD for 2-digit date, etc. The function of each character in the format string will be described in detail below, and the corresponding format result will be displayed with the data in C1 as an example:

Character	Fuction	SPL	Result
y/yy	year，2 digits	=string(C1,"yy")
yyyy	year，4 digits	=string(C1,"yyyy")
M	month	=string(C1,"M")
MM	month，2 digits，Make up with 0	=string(C1,"MM")
MMM	month，English abbreviation	=string(C1,"MMM")
MMMM	month，full English word	=string(C1,"MMMM")
d	day	=string(C1,"d")
dd	day，2 digits，Make up with 0	=string(C1,"dd")
E	Week，English abbreviation	=string(C1,"E")
EEEE	week，full English word	=string(C1,"EEEE")
G	Era identifier，BC/AD，abbreviation	=string(C1,"G")
w	Number of weeks of the year	=string(C1,"w")
ww	Number of weeks of the year，2 digits，make up with 0	=string(C1,"ww")
W	Number of weeks of the month	=string(C1,"W")
F	Number of weeks of the month，only calculated on a daily basis	=string(C1,"F")
D	Number of days of the year	=string(C1,"D")
H	hour，24-hour system, 0-23	=string(C1,"H")
HH	hour，24-hour system, 0-23，2 digits，make up with 0	=string(C1,"HH")
k	hour,24-hour system,1~24	=string(C1,"k")
kk	hour,24-hour system,1~24，2 digits，make up with 0	=string(C1,"kk")
h	hour，12-hour system，1~12	=string(C1,"h")
hh	hour，12-hour system，1~12，2 digits，make up with 0	=string(C1,"hh")
K	hour，12-hour system，0~11	=string(C1,"K")
KK	hour，12-hour system，0~11，2 digits，make up with 0	=string(C1,"KK")
m	minute	=string(C1,"m")
mm	minute，2 digits，make up with 0	=string(C1,"mm")
s	second	=string(C1,"s")
ss	second，2 digits，make up with 0	=string(C1,"ss")
S	Millisecond	=string(C1,"S")
a	AM/PM	=string(C1,"a")
z	Time zone，abbreviation	=string(C1,"z")
zzzz	Time zone，Full spell	=string(C1,"zzzz")
Z	Time zone code	=string(C1,"Z")

 

3 Get information from date time data

In date time and other types of data, the specific year, month, day, hour, minute and second are often the information that needs to be used separately during processing. We can obtain the components of these date time data through the functions of year (), month (), day (), hour (), minute (), second (), millisecond ():

	A	B	C
1	2019-6-30	12:45:30.230	=now()
2	=year(A1)	=month(A1)	=day(A1)
3	=hour(B1)	=minute(B1)	=second(B1)
4	=month(C1)	=hour(C1)	=millisecond(C1)

The date time data in A1, B1 and C1 are as follows:

 

A2, B2 and C2 respectively obtain year, month and day from date data:

 

A3, B3 and C3 obtain hour, minute and second from time data：

 

A4, B4, and C4 get the month, hour, and millisecond components from the date time type result of the now() function：

 

As you can see from the results, the result returned by the now()function is accurate to milliseconds, but only seconds are displayed. When using the now() function, you can change the precision of the result by adding options. Such as:

	A	B	C
1	=now@d()	=now@t()
2	=now@m()	=now@s()	=millisecond(B2)

Add the @d option in A1, only take the date part of the data, add the @t option in B1, only take the time part of the data, the results are as follows:

 

Add the @m option in A2, and the data will be accurate to minute. Add the @s option in B2, and the data will be accurate to second. The results are as follows：

 

As you can see in C2, the data millisecond component in B2 is 0：

 

The @m and @s options can also be used in the datetime()and time() functions to set the precision of converting date time data and time data to minutes or seconds.

 

In date type data, you can also obtain time component, and in time type data, you can also obtain date component, such as:

	A	B	C
1	2019-6-30	19:05:10.866
2	=hour(A1)	=minute(A1)	=second(A1)
3	=month@y(B1)	=day(B1)

The results in A2, B2 and C2 are as follows：

 

That is, a standalone date data in which the time is specified as 00:00:00.

The month function in A3 adds the @y option to get the six digits of the month and year. The results in A3 and B3 are as follows：

 

That is, the standalone time data, where the date is January 1, 1970。

 

In addition to getting components directly from date time type data, there are also some functions that can be used to get date related data.            

Use day@w() to add the @w option when getting the date component to get the day of the week:

	A	B	C
1	2019-4-3	2019-6-18	2020-2-20
2	=day@w(A1)	=day@w(B1)	=day@w(C1)
3	=string(A1,"EEEE")	=string(B1,"EEEE")	=string(C1,"EEEE")

A2, B2 and C2 get the day of the week respectively, and the results are as follows:

 

In SPL, the first day of the week is calculated from Sunday. In line 3, the day of the week is obtained by displaying the string:

 

 

In addition, we can use the pdate () function to get the date with different options.

	A	B	C
1	2019-8-17
2	=pdate@w(A1)	=pdate@m(A1)	=pdate@q(A1)
3	=pdate@we(A1)	=pdate@me(A1)	=pdate@qe(A1)

In the pdate() function:

○   Use the @w option directly to get the first day of the week, counting from Sunday;

○   Add @m option to get the first day of this month;

○   Add @q option to get the first day of the quarter;

○   Add @e to get the data of the last day of a certain period, such as the last day of this week, the last day of this quarter, etc. The results of A2, B2, C2, A3, B3 and C3 are as follows:

 

 

 

In SPL, you can also use the days() function to calculate the total days of the month of a certain date, add the @q option to get the total days of the quarter, and add the @y option to get the total days of the whole year, such as:

	A	B	C
1	2019-02-21
2	=days(A1)	=days@q(A1)	=days@y(A1)

The results in A2, B2 and C2 are as follows：

 

4 Calculation using date time data

In addition to obtaining information directly from date and time data, date and time data can also be used to perform various calculations in SPL.            

The most commonly used calculation of dates is age:

	A	B	C
1	1995-3-30	=now@d()
2	=age(A1)	=age@m(A1)	=age@y(A1)

The data in A1 and B1 are as follows：

 

In line 2, use the age() function to calculate the age according to the birthday in A1. When calculating the age, it is based on the current date. By default, it will be accurate to the day. Add @m to set the precision to the month, and add @y to set the precision to the year. In case of different precision, the calculated ages may be different. The ages obtained in A2, B2 and C2 are respectively as follows:

 

 

Using the age ()function is similar to calculating the number of years between the birthday date and the current date. The more common function to calculate the time interval is the interval() function. With this function, you can calculate how many days the difference between two date and time data is. Add @y, @q, @m, @s, @ms and other options,you can calculate the year, quarter, month, second or millisecond of the interval. For example:

	A	B	C
1	1995-4-30	2019-4-10
2	=interval(A1,B1)	=interval@y(A1,B1)	=B1-A1

If you only need to calculate the difference between the two dates, you can also use subtraction directly. The results in A2, B2 and C2 are as follows：

 

In particular, each date time data can be converted to a corresponding long integer, which is actually the interval of  milliseconds between the specified date time and 0:00:00 GMT of January 1, 1970, such as:

	A	B
1	=datetime("1/1/1970 0:00:00   GMT","m/d/yyyy H��ss z")	1995-4-30
2	=interval@ms(A1,B1)	=long(B1)

In B2, the date time data is directly converted to long integers by using the long() function. You can see that the results in A2 and B2 are the same：

 

The elapse(t,k) function in SPL can calculate the result after k days according to the existing date time t. Add the option @y to calculate the date time after k years. Similarly, you can add @q, @m, @s, @ms a options to set the interval unit to quarter, month, second or millisecond. Such as: 

	A	B	C
1	1995-4-30
2	=elapse(A1,10)	=elapse@y(A1,20)	=elapse@m(A1,-1)

The dates after 10 days, 20 years and 1 month before are calculated in A2, B2 and C2 respectively, and the results are as follows:

If it is only necessary to calculate the date with a difference of several days, it can also be directly simplified as the addition and subtraction, such as = a1 + 10, = a1-10, etc.

Because the days of each month are different, whether the current date is the last day of the month will be considered by default when calculating the date after k months, and corresponding adjustments will be made. If such adjustments are not needed, the @e option needs to be added, such as:

	A	B	C
1	2020-2-29	=elapse@m(A1,3)	=elapse@me(A1,3)

The results in B1 and C1 are as follows:

 

It can be seen that since February 29, 2020 is the last day of February, when calculating the date after 3 months in B1, the last day of May is also obtained, while the @e option is added in C1, which only calculates the date after 3 months without adjustment. 

Due to the specialness of date and time data, it is not convenient to directly determine whether they are equal in many cases. For this reason, you can use the deq() function in SPL, as long as two data are on the same day, they are considered equal. You can also add options @y, @q, @m, @t, @w, to set the accuracy to year, quarter, month, ten day or week. Such as:

	A	B	C
1	2019-2-15 12:33:00	2019-2-15 18:45:20	2019-2-28 10:05:00
2	=deq(A1,B1)	=deq(A1,C1)	=deq@m(A1,C1)

The judgment results in A2, B2 and C2 are as follows：

 

When using date and time data, another kind of calculation is related to the calculation of workdays. Workday(t,k,h) function can be used to calculate the date and time of k workdays after the specified date and time t. Workdays(b,e,h) can be used to calculate the sequence of workdays between start date b and end date e. The calculation of working days is complex. The normal working day is from Monday to Friday every week, but sometimes it is affected by public holidays. One day from Monday to Friday is a rest day, or one day on the weekend needs to work normally. Such date adjustment can be set to sequence h. Non weekend dates in the sequence are added holidays, while weekend dates in the sequence are normal working days. Such as: 

	A	B	C
1	2019-4-30	2019-4-27	2019-5-5
2	[2019-5-1, 2019-5-2, 2019-5-3,   2019-4-28, 2019-5-5]
3	=workday(A1,2,A2)	=workdays(B1,C1)	=workdays(B1,C1,A2)

In A2, the workday adjustment of public holiday of May 1 Labor Day 2019 is set: the original working day from May 1 to May 3 is off, while the original weekend on April 28 and may 5 is adjusted as the working day. The calculation result in A3 is as follows:

 

Note that may 1-3, 2019 is the Labor Day holiday, May 4 is the weekend, and the original weekend of May 5 is adjusted to the working day, so the second working day after April 30, 2019 is May 6, 2019.

The results in B3 and C3 are as follows：

 

As you can see, if the adjustment date sequence is not specified, only the Monday to Friday dates will be displayed; the result returned in C3 is the correct working day. 

Workdays() can be used to get the working day sequence. A more general function to calculate the date time sequence is periods(s,e,i), which is used to calculate the date time sequence between the start date time b and the end date time e. the interval is i days. By adding the function options @y, @q, @m, @t, @s, you can adjust the unit of time interval to year, quarter, month, ten day or second. If you do not want the set end value to appear in the result, you can add the @x option. In the time series, each data that appears will be adjusted to the first day of the specified time period, such as the first day of each month, the first day of each year, etc. if you do not need this adjustment, you can add the @o option. Such as:

	A	B	C
1	2019-1-14	2019-1-20	2019-4-1
2	=periods(A1,B1)	=periods@m(B1,C1)
3	=periods@xm(B1,C1)	=periods@om(B1,C1)	=periods@oxm(B1,C1)

The results in A2 and B2 are as follows：

 

The interval unit in A2 is day, and the interval unit in B2 is month.

In A3, the removal of end date is set, in B3, the non-adjustment of date to the beginning of the month is set, and in C3, the @o and @x options are set at the same time. The results are as follows:

 

 

Sometimes it is necessary to divide a period of time equally. In this case, you can use the range() function, such as:

	A	B	C
1	2019-1-1	2020-1-1
2	=range(A1,B1,1:4)	=range(A1,B1,3:4)	=range(A1,B1,4)

In A2, the period from January 1, 2019 to January 1, 2020 is divided into 4 segments on average, and the start and end dates of the first segment are taken out; in B2, the start and end dates of the third segment are taken out. In C2, each interval date incl. start and end time are listed. The results in A2, B2 and C2 are as follows:

 

When using the range function, if the first two parameters are date, the segmentation will be accurate to the day; if the first two parameters are date time, the segmentation will be accurate to the second.