Date and Time Functions in esProc

Date and time data has its own characteristics for analysis and handling. In this article let’s look at how to perform date and time handling in esProc.

1. Conversion to date and time data

Usually the date and time data is entered or displayed as strings. With esProc, you can click Tool>Option to set the default format for date and time data on the Environment tab. For example:

Then the date and time data will be displayed as the default format in an esProc cellset. Here’s an example:

A1 gets result as follows:

The now() function is often used to obtain the current system date and time in date and time handling.

When entering a date/time/datetime constant, enter it in the default format as a string. esProc will automatically parse the constant into date and time data. For example:

A1, B1 and C1 will be parsed into the date data, time data and datetime data respectively, as shown below:

If the data is string type, instead of a constant directly entered in esProc, use date() function, time() function or datetime() function to convert the string data to the date data, time data or datetime data. For example:

Below are the strings in A2, B2 and C2:

The code in the third line uses ifdate() function and iftime() function to check if the strings in the second line have been converted to the date data or time data or datatime data. Note that ifdate() function is used to find if a value is a date type or a datetime type. A3, B3 and C3 respectively have the following results:

In the fourth line, strings are converted into the date data, time data and datetime data according to the specified formats. Here’re the results:

In the fifth line, same functions are used to check if the values in the fourth line are date type, time type or datetime type. A5, B5 and C5 get results as follows:

For the external data, sometimes you need to handle various formats of date data, time data, and datetime data. In this case, you can add a display format string after a string when using date() function, time() function or datetime()function to convert the data type. For example:

Values in the first line are not in the default date, time and datetime formats. The value in B1 adds the character ‘ at the beginning to show that this is a string constant. Below are values of A1, B1 and C1:

The second line lists the display format strings for the values in the first line. Then in the third line, functions for converting values to the date data, time data and datetime data specify the display formats for them. Below are the results of A3, B3 and C3:

With the type conversion, values will be shown in the default format when being viewed. For more details about the display formats of the date and time data, see Data Display Formats in esProc. By defining the display format with the string(d,fmt) function, you can transform the date and time data to strings of the specified formats. For example:

A3, B3 and C3 convert the date, time and datetime data into the string data:

You can also directly change the default display format for the date and time data as required.

When using date() function, time() function and datetime() function to generate data of the corresponding types, you can directly specify the quantities for the year, month, day, hour, minute and second respectively. For example:

Below are the result of A1, B1 and C1:

Pay attention to the proper and reasonable range of each of the above integers when assigning quantities to them. For instance, the value range of hour is 0~23.

2. Obtaining information from date/time/datetime data

The date data, time data and datetime data holds a lot of information, like the year, month, day, hour, minute and second. esProc provides functions such as year(), month(), day(), hour(), minute(), second(), and millisecond() to get values of these parts. For example:

Below are the date, time and datetime data in A1, B1 and C1:

A2, B2 and C2 obtain the value of each part from the date value:

A3, B3 and C3 obtain the value of each part from the time value:

A4, B4 and C4 obtain the values of month, hour and millisecond from the datetime value of the now() function:

As can be seen, the now() function returns a result accurate to the millisecond. But you can make it return results of different degrees of accuracy by adding different options. For example:

A1 adds @d option to get only the date part, and B1 adds @t option to get merely the time part. Below are their results:

A2 uses @m option to have a result accurate to minute, and B2 uses @s option to get a result accurate to second. Below are the results:

You can see from the result of C2 that the value of millisecond part of B2 is 0:

Both @m option and @s option can be used to set the degree of accuracy as minute or second by datetime() function and time() function in conversion to datetime data and time data.

You can obtain the time part from date data, and get date part from time data. For example:

Below are the results of A2, B2 and C2:

It can be seen that the default time in a date value is 00:00:00.

Below are the results of A3, B3 and C3:

So you can see that the default date in a time value is January 1, 1970.

Apart from getting the component values directly from date/time/datetime data, there’re also some esProc functions for getting date information.

You can use @w option in the day() function, which gets the date, to find the ordinal number of the given date in the week to which it belongs:

AA2, B2 and C2 find the ordinal numbers of the given dates in their respective weeks. The results are as follows:

According to the results, a week begins on Sunday. To give you a clearer view, the code in the third line determines the day of the week for each of the given dates with a display format string:

The pdate() function can work with different options to get the corresponding dates:

Use @w option in pdate() function to get the date of the first day of the defined week that begins with Sunday; use @m option to get the date of the first day of the defined month; use @q option to get the date of the first day of the defined quarter; and use @e option with any of the other options to get the date of the last day of the defined time period, like a week and a quarter. Below are the respective results of A2, B2, C2, A3, B3 and C3:

The days() function by default calculates the number of days in the month to which a certain date belongs. Along with @q option, the function can calculate the number of days in the quarter to which the date belongs; and by working with @y option, it calculates the number of days in the year to which the date belongs. For example:

Below are the results of A2, B2 and C2:

3. Date and time handling

Besides obtaining information directly from the date and time data, you can also use them to perform various operations in esProc. The most common date handling operation is to calculate age:

The values of A1 and A2 are as follows:

The age() function in the second line calculates the age according to A1’s date of birth and the current date. By default the function returns a result accurate to the day, but it will return one accurate to the month or the year with @m option or @y option added. With different degrees of accuracy, the obtained age could be different. The results of A2, B2 and C2 are as follows:

What the age() function gets is similar to calculating the number of years between the date of birth and the current date. But to find the interval between two dates, the interval() function is more widely used. It by default finds the number of days between two dates, but, by working with options such as @y, @q, @m, @s and @ms, it can find the number of years, quarters, months, seconds and milliseconds between the two dates. For example:

If you just need to find the number of days between two dates, do it through subtraction. Below are the results of A2, B2 and C2:

By the way, each date/time/datetime value can be converted to a long integer, which is in effect the number of milliseconds between the value itself and 0:00:00 January 1, 1970 GMT. For example:

B2 uses long() function to convert the date data to a long integer directly. Both A2 and B2 get the same result:

The after(t,k) function finds the datetime value which is k days after the given datetime value t. With the option @y, the function gets the datetime value which is k years later. Similarly, options including @q, @m, @s and @ms can be added to get a datetime value based on the time unit of the quarter, the month, the second and the millisecond. For example:

A2, B2 and C2 respectively find the date ten days later, the date 20 years later, and the date one month before. Below are their results:

To find the date which is certain days before or after a given date, do it in the simple way of subtraction, such as =A1+10，=A1-10.

Since not all months have the same number of days, the function for getting the date which is k months after a given date will by default check if the resulting date is the last day of the month to which it belongs and modify it to the last day if it isn’t. Use @e option if the modification isn’t wanted. For example:

Below are results of B1 and C1:

As February 29, 2016 is the last day of the month, B1 also gets the last day of May which is 3 months later. But by using @e option, C1 only finds the date which is exactly 3 months after the given date without making any adjustment.

Generally it’s inconvenient to compare two datetime values in a direct way. But esProc offers deq() function make the comparison. If the dates in the two values are the same, then the two values are regarded as equal. Options including @y, @q, @m, @t and @w can be added to set the degree of accuracy as the year, the quarter, the month, the period of ten days and the week. For example:

Below are the values of A2, B2 and C2:

Another type of date and time handling is about the workdays. The workday(t,k,h) function finds the date that is the kth workdays after the given date t. The workdays(b,e,h) function gets a sequence of workdays between the starting date b and the ending date e inclusive. The computation related to the workdays is slightly complicated. Normally the weekdays are Monday through Friday, but owing to the public holidays, sometimes certain weekdays become holidays, or a certain weekend day becomes workday. In this case, h, a sequence of special dates, will be used, where the non-weekend date is a holiday and the weekend day is a workday. For example:

A2 sets a sequence of public holidays in January, 2016, including the New Year’s Day and the Martin Luther King Day. Below is A3’s result:

Because the January 1, 2016 is the New Year’s Day and the following January 2 and 3 are weekends, the second workday after December 31, 2015 is January 5, 2016.

Below are the results of B3 and C3:

Without specifying a sequence of dates of adjusted workdays and holidays, January 18, 2016 is still a workday.

Though the workdays() function is used to generate a sequence of workdays, another function periods(s,e,i) is more commonly used to generate a sequence of date, time or datetime values between the given starting date b and ending date e at a specified interval of every i day(s). With the options @y, @q, @m, @t and @s added, the unit of the time interval could be the year, the quarter, the month, the period of ten days or the second. If the specified ending date isn’t wanted, use @x option. In this sequence of date, time or datetime values, each value will be automatically displayed as the first date of the specified time unit, such as the first day of the month or the year. Use @o option to cancel the automatic adjustment. For example:

Below are the results of A2 and B2:

The unit of time interval in A2 is the day, and the that in A3 is the month.

A3 discards the ending date using @x option, B3 won’t automatically adjust the generated date into the first day of the month with @o option, and C3 use both options. Below are the results of A3, B3 and C3: