Shulou(Shulou.com)06/02 Report--

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

1 conversion and generation of date and time data

Date and time data, usually entered or displayed as a string. When using the aggregator, you can click Tool > Option, and on the Environment page of the option configuration, set the default format for date-time type data, such as:

When using SPL, date-time type data is displayed in the default format, such as:

A1=now ()

After running, the results in A1 are as follows:

The function now () used here is a commonly used function in date-time calculation, which can get the current date-time in the system. The display of date and time varies in different languages, especially month and week data, which will be illustrated in the English version below.

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

ABC12019-2-112 4515 02019-02-01 10:30:00

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

SPL can parse directly entered constants into date-time types, but for those that are already of string type, you need to convert the string to date, time, or date-time data using date (), time (), or datetime (), such as:

ABC120192202=A1/ "-" / B1 / "-" / C1F12 / ":" / 22 / ": 00" = A2+ "+ B23=ifdate (A2) = iftime (B2) = ifdate (C2) 4=date (A2) = time (B2) = datetime (C2) 5=ifdate (A4) = iftime (B4) = ifdate (C4)

Strings obtained by string operations in A2PowerB2 and C2 are:

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

As can be seen from the results, none of the cell values in row 2 are date or time data, they are actually strings, and the display is different from the date and time.

In line 4, the string is converted to date-time data in the corresponding format, and the result is as follows:

In line 5, it is determined whether the data in row 4 has been processed as a date or time type. The result is as follows:

When using external data, sometimes you need to deal with date-time data in different formats. When using date (), time (), or datetime (), you can add the display format string used after the string, such as:

ABC1Feb 2, 2019 February 2 PM'2019 30 PM'2019-6-20 2:30:45 PM2MMM dlanguage yyyh Ⓜ s ayyyy-M-d h Ⓜ s a3=date (A1 Magi A2) = time (B1 Magi B2) = datetime (C1 Magi C2)

None of the data in row 1 uses the default date-time format, and both B1 and C1 are preceded by the 'character to indicate the use of string constants. The data in A1 _ line B1 and C1 are as follows:

It should be noted that the setting of the month format MMM is related to the locale, the English abbreviation for the month in the English environment, such as Feb;, and the Chinese month in the Chinese environment.

The format string listed in line 2 tells SPL how to parse the data in line 1, and line 3 uses these format strings for conversion. The result is as follows:

After the type conversion is completed, the results are still displayed in the default format. If you want to display in a different format, you can use the string (dfocus FMT) function to convert date-time class data to a string in the specified format, such as:

ABC12019-02-2112 10:30:002MMMM DJ Yyyh Ⓜ s aMMM d Letters yyyh Ⓜ s a3=string (A1 Magi A2) = string (B1 Magi B2) = string (C1 Jer C2)

In A3B3 and C3, date-time class data is converted to a string in the specified format:

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

When you use date (), time (), or datetime () to generate data types, you can also directly specify year, month, day, hour, minute, second, and so on:

ABC1=date (20119, 2jue 21) = time (135jue 0) = datetime (20119pr 12jue 29jue 13je 5e 0)

The results are as follows:

When specifying each component, pay attention to the reasonable range of each integer, for example, the hour component is between 023.

2 display format of date and time data

ABC1=date (201906 ~ 29) = time (13:5:18) = datetime (A1 ~ ~ B1)

In this example, A1 uses date () to generate the date, and the first parameter uses six digits, which SPL will use to represent both the year and the month. On the other hand, C1 combines the date in A1 and the time in B1 to get the date-time data, and the result is as follows:

The previous section has used some display formats of date-time data, using format strings to specify the display style, such as yyyy for a 4-digit year, dd for a 2-digit date, and so on. The function of each character in the format string is described in detail below, and the corresponding formatting results are displayed by taking the data in C1 as an example:

Character action SPL result y/yy year, two digits = string (C1, "yy") yyyy year, four digits = string (C1, "yyyy") M month = string (C1, "M") MM month, two digits, less than 0 complete = string (C1, "MM") MMM month, English abbreviation = string (C1, "MMM") MMMM month, English full spelling = string (C1, "MMMM") d = string (C1, "d") dd day, two digits Insufficient 0 complement = string (C1, "dd") E week, abbreviation = string (C1, "E") EEEE week, full spelling = string (C1, "EEEE") GEra identifier, BC / AD, abbreviation = string (C1, "G") w week of this year = string (C1, "w") ww week of this year, double digits Insufficient complement = string (C1, "ww") W week of this month = string (C1, "W") F in the week of this month, only on the basis of daily calculation = string (C1, "F") D the day of this year = string (C1, "D") H-hour, 24-hour system, 0~23=string (C1, "H") HH hour, 24-hour system, 0,23, double digits, insufficient complement 0 = string (C1, "HH") k-hour, 24-hour system 1~24=string (C1, "k") kk hours, 24 hours, 1: 24, double digits, less than 0 to complete = string (C1, "kk") h hours, 12 hours, 1~12=string (C1, "h") hh hours, 12 hours, 1: 12, double digits, less than 0 to complete = string (C1, "hh") K hours, 12 hours, 0~11=string (C1, "K") KK hours, 12 hours, 0,11, double digits Less than 0 to complete = string (C1, "KK") m minutes = string (C1, "m") mm minutes, two digits, less than 0 to complete = string (C1, "mm") s seconds = string (C1, "s") ss seconds, double digits, under 0 completion = string (C1, "ss") S milliseconds = string (C1, "S") a morning / afternoon = string (C1, "a") z time zone, abbreviation = string (C1, "z") zzzz time zone Full spelling = 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, specific year, month, day, hour, minute, second and so on are often the information that needs to be used separately when processing. We can obtain each component of these date-time data through year (), month (), day (), hour (), minute (), second (), millisecond () and other functions:

ABC12019-6-3012: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 and time data in A1 PersonalB1 and C1 are as follows:

A2PowerB2 and C2 obtain the year, month and day from the date data, respectively:

A3MagneB3 and C3 acquire hours, minutes and seconds from time data:

A4B4 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 to seconds when displayed. When using the now () function, you can change the precision of the result by adding options. Such as:

ABC1=now@d () = now@t ()

2=now@m () = now@s () = millisecond (B2)

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

If the @ m option is added to A2, the data will be accurate to points, and the @ s option will be added to B2, and the data obtained will be accurate to seconds. The results are as follows:

As you can see in C2, the millisecond component of data 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.

The time component can also be obtained in the date type data, and the date component can also be obtained from the time type data, such as:

ABC12019-6-3019 purl 05purl 10.866

2=hour (A1) = minute (A1) = second (A1) 3=month@y (B1) = day (B1)

The results in A2Pol B2 and C2 are as follows:

That is, separate date data where the time is specified as 00:00:00.

The month function in A3 adds the @ y option to get a 6-digit number of years and months. The results in A3 and B3 are as follows:

In other words, separate time data, where the date is January 1, 1970.

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

Using day@w (), you can get the day of the week by adding the @ w option when getting the date component:

ABC12019-4-32019-6-182020-2-202=day@w (A1) = day@w (B1) = day@w (C1) 3=string (A1, "EEEE") = string (B1, "EEEE") = string (C1, "EEEE")

The days of the week are the days of the week, respectively, and the results are as follows:

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

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

ABC12019-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:

○ directly uses the @ w option to get the date of the first day of the week, starting on Sunday

○ add @ m option to get the date of the first day of this month

○ adds the @ Q option to get the date of the first day of the quarter

If you add @ e to ○, you can get data for the last day of a certain period of time, such as the last day of the week, the last day of the quarter, etc. The results in A2, B2, C2, A3, B3 and C3 are as follows:

In SPL, you can also use the days () function to calculate the total number of days in a month on a date. Add the @ Q option to get the total number of days in the quarter, and add the @ y option to get the total number of days for the whole year, such as:

ABC12019-02-21

2=days (A1) = days@q (A1) = days@y (A1)

The results in A2PowerB2 and C2 are as follows:

4 calculation using date-time data

In addition to getting information directly from date-time data, you can also use date-time class data to perform various calculations in SPL.

The most commonly used calculation of the relevant date is to calculate the age:

ABC11995-3-30=now@d ()

2=age (A1) = age@m (A1) = age@y (A1)

The data in A1 and B1 are as follows:

In the second line, 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 the case of different accuracy, the calculated ages may be different. The ages obtained in A2 and C2 are as follows:

Use the age () function, which is similar to calculating the number of years between the birthday date and the current date. A more common function to calculate the time interval is the interval () function, which can be used to calculate the difference between the two date-time data in days. Add options such as @ yforce, QQ, mand, and so on, to calculate the interval of years, quarters, months, seconds or milliseconds. For example:

ABC11995-4-302019-4-10

2=interval (A1 Magi B1) = interval@y (A1 Magi B1) = B1-A1

If you only need to calculate the difference between two dates by how many days, you can also use subtraction directly. The results in A2 and C2 are as follows:

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

AB1=datetime ("1gamma 0:00:00 GMT", "m/d/yyyy H"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.