Time related processing and calculation are also common. SPL has three data types: date, time and datetime.

Date data has only date information but no time information; Time data has only time information and no corresponding date information; Datetime has both date and time, which is equivalent to a certain moment.

It is necessary to provide a time type without date information. Some events that occur on an uncertain date do not have date information, and it is more appropriate to use a time type, an extra date may cause confusion. For example, calculate the interval between 8:00 and 9:00. If there must be a date information, we have to pay attention to whether it is 8:00 and 9:00 of the same day, which is easy to be neglected.

But with a more precise datetime type, why should there be an imprecise date type?

This is similar to integer and floating-point number, because for many events, we only care about the date of occurrence and no longer care about the time. If only datetime type is used, the amount of storage and calculation will become larger. Moreover, when comparing whether the two dates are equal, we should also pay attention to whether the attached time information is also equal, and errors are likely to occur.

Not all programming languages provide these three data types, and most databases have only datetime and no date.

The most basic operation related to date and time is the splitting and combination of components:

	A	B	C
1	2020-12-20	=date(2020,12,2)
2	=year(A1)	=month(A1)	=day(A1)
3	22:3:5	=time(22,3,5)
4	=hour(A3)	=minute(A3)	=second(A3)
5	2020-12-2 10:3:5	=datetime(2020,12,2,10,3,5)	=datetime(A1,A3)
6	=year(A5)	=month(A5)	=day(A5)
7	=hour(A5)	=minute(A5)	=second(A5)
8	=date(A5)	=time(A5)

These functions are very simple. Just look at the examples, and there is no need to explain them in detail.

SPL also supports putting the month and year together as a component, that is, a six-digit number.

	A	B	C
…	…
9	=date(202012,20)	=month@y(A9)

Sometimes you will find this kind of processing very convenient.

The conversion between date time and string is troublesome, and there are many kinds of formats. For example, 2020-12-20 may also be written as 2020/12/20, 12/20/2020, 20-12-2020, Europeans and Americans may also use the format Dec/20/2020 or 20-Dec-2020. The formats of time are relatively less, but it may also be written as 10:03:05 PM or 22:3:5.

Therefore, a program language that can handle date and time usually provides the ability of format parsing and conversion.

	A	B
1	2020-12-2	22:3:5
2	=string(A1,“yyyy/MM/dd”)	=string(B1,“hh🇲🇲ss”)
3	=string(A1,“MM/dd/yyyy”)	=string(B1,“HH/:m/:s”)
4	=string(A1,“MMM-d-yy”)	=string(B1,“h/:m/:s a”)
5	=string(A1,“d/MMM/yyyy”)
6	=date(“2020-12-20”)	=time(“22:3:5”)
7	=date(“DEC/20/20”,“MMM/d/yy”)	=time(“10:3:5 pm”,“h/:m/:s a”)
8	=datetime(A1,B1)	=string(A8,“MM mm”)

The writing method of these format strings can be found in the function help. It is almost a common rule all over the world, and we will not explain in detail here. You only need to know this usage. It should be emphasized that since the starting letter of month and minute are both m, it is necessary to distinguish them by uppercase and lowercase. You can observe B8.

By the way, numerical values also have format problems,

	A	B
1	12345.23456	12345678
2	=string(A1,“#.00”)	=string(B1,"#")
3	=string(A1,“#.0”)	=string(B1,“#,###”)
4	=string(A1,“#,###.0000000000”)

These are not explained in detail. Just try it yourself.

As we said earlier, there are only numbers in the computer, and other data types are actually numbers by some kind of coding. For example, the string in SPL is encoded by unicode, so how is the date and time encoded?

The internal codes of date and time data types are long integers. Let’s observe their rules:

	A	B	C
1	2020-12-1	2020-12-2
2	=long(A1)	=long(B1)	=B2-A2
3	0:0:0	0:0:1
4	=long(A3)	=long(B3)	=B4-A4
5	=date(0)	=time(0)

First observe C2, two dates with a difference of 1 day, after converting to long integer, the difference is 86400000. What is this number?

Readers who often do date calculation will sensitively find that 86400=24*60*60, and it is exactly 1000 times the number of seconds of a day. Observe C4. The difference between two dates with a difference of 1 second is 1000 after converting to a long integer. This confirms our idea that the long integer corresponding to date and time is the number of milliseconds from a certain moment.

Then, from which moment? We can’t figure it out by observing A1, B1, A4 and B4 directly, we can simply reverse it and observe A5 and B5. It turns out that the date is counted from January 1, 1970. This is an agreement of the International Computer Standards Organization. Dates earlier than 1970 should be expressed as negative numbers. Interestingly, several days in history are not exactly 86400 seconds, and the long integer value corresponding to a date is not always the number of days from January 1, 1970 multiplied by 86400000. Interested readers can program to find out which days (with the skills we have learned till now readers should be able to do this), and then search the Internet to see why these dates are not 86400 seconds.

Let’s look at the time, the result of B5 is strange. I calculate it here as 8 o’clock. Why not start from 0 o’clock?

Because I am writing this book in Beijing, China. The time zone of Beijing is East Zone 8, and 8:00 Beijing time is exactly 0:00 GMT. The time is counted from 0:00, but it is Greenwich mean time, not the local time zone. If you happen to be in the UK now, the calculated B5 will be 0.

There are so many interesting things about date and time.

Let’s continue to learn about date and time operations. The most common requirement is to calculate the gap between two moments and the time after a period of time from a certain moment.

For example, if we want to see how effective the optimization method used in the calculation of Narcissus number is, we need to calculate the execution time of this program:

	A	B	C	D	E	F
1	=now()
2	for 1000	=0	=[]
3		for 9	=100*B3	=B3*B3*B3
4			for 0,9	=C3+10*B4	=D3+C4*C4*C4
5				for 0,9	=D4+D5	=E4+D5*D5*D5
6					if E5==E5	>C2.insert(0,E5)
7	=now()	=interval@ms(A1,A7)

Here we need to use the code that generates the result sequence, because the output method will involve screen display. In fact, this action is very complex, and it may take longer than completing these calculations, and it is not the test of calculation time. Moreover, we repeated this action 1000 times, because the CPU of modern computers is so fast that we can’t see the difference by only executing it once.

now()of A1 will return the time when this statement is executed, that is, record the start time of the program in A1. At A7, use now() to get the time when the program ends, and then use inteval@ms() to calculate the gap between the two moments, and write the two parameters in chronological order, so as to get the execution time of the program.

now()returns the datetime type, which can be accurate to milliseconds. The @ms option of interval() means that the calculated time gap is accurate to milliseconds. Because the program executes very fast, even if it is repeated 1000 times, if it is not accurate to milliseconds, we still can not see the gap.

Now do the same transformation to the optimized code and execute it:

	A	B	C	D	E	F	G
1	=now()
2	for 1000	=0	=[]
3		for 9	=100*B3	=B3*B3*B3
4			for 0,9	=C3+10*B4	=D3+C4*C4*C4		break
5				for 0,9	=D4+D5	=E4+D5*D5*D5
6					if E5==E5	>C2.insert(0,E5)
7					else if E5<F5	break
8	=now()	=interval@ms(A1,A8)

Comparing these two time gaps, we can know whether optimization works. On my computer, the optimized code is about 25% faster, which is pretty obvious.

The interval()function has various options and can return the time gap of different accuracy. In daily work, the most common task is to calculate the number of days between two dates, which is also the default return value of the interval() function when the option is not used. Moreover, because it is too common, SPL simplifies this calculation to be expressed directly by subtraction, that is, interval(d1,d2)==d2-d1.

Someone borrowed 25000 yuan on December 4, 2018, with a daily interest of 0.013%. If he pays it back today, how much interest should he pay?

	A
1	=25000*0.013/100*interval(date(2018,12,4),now())
2	=25000*0.013/100*(now()-date(2018,12,4))

The results of A1 and A2 are the same. now() contains date information, and can be used as today.

Let’s make the case a little more complicated. If the interest is added to the principal at the end of each year and the following interest is calculated on this basis, how much should be paid in total when it is returned today?

In other words, on January 1 of each year, the interest at that time shall be calculated and added to the principal, and the following interest shall be calculated on this basis. We use a clumsy way to calculate, looping day by day from the loan date to today.

	A	B	C	D
1	2018-12-4	0.013%	=25000	=0
2	for now()-A1	if month(A1)==1 && day(A1)==1	>C1+=D1	>D1=0
3		>A1=elapse(A1,1)	>D1+=B1*C1
4	=C1+D1

The current interest is stored in D1. The initial value is 0. For every day of the loop, the interest will be increased by one day. If the current date is January 1, add the current interest to the principal and then clear the interest. At the end of the loop, add the principal and interest at that time.

It should be noted that in cell B3, the elapse(d,n) function returns the date n days after a date d, with this action, the loop can run normally.

Like interval(), elapse() has many options to control accuracy. Moreover, when the accuracy is days, it can be expressed directly by addition. That is, cell B3 can be simply written as >A1=A1+1, or even >A1+=1.

The percentage constant can be directly expressed by % in a cell, but not in the expression, % will be regarded as the operator of remainder.

---

SPL Programming - Preface
SPL Programming - 7.2 [String and time] Split and concatenate
SPL Programming - 8.1 [Data table] Structured data