Serial datetimes

Analytics uses serial datetime values to store dates, datetimes, and times, and to perform datetime calculations.

You may encounter a serial datetime value when working with datetime expressions. For example, subtraction operations that involve time values return results in the form of serial times.

What is a serial datetime?

Serial datetimes are numbers that use integers to represent dates, and a decimal fractional portion of 24 hours to represent times. The portion before the decimal point is the date, and the portion after the decimal point is the time.

Serial datetime	Regular datetime equivalent
42004	01 January 2015
42004.5000000	01 January 2015 12:00:00
0.7500000	18:00:00
42004.74618055555556	01 January 2015 17:54:30

The date portion

The date portion is the number of days that have elapsed since 01 January 1900. A serial date of ‘1’ is equivalent to 02 January 1900. A serial date of ‘0’ (zero) is not counted. A serial date of ‘42004’ is equivalent to 01 January 2015.

The time portion

The time portion of serial datetimes uses the 24-hour clock. The serial time value is calculated as follows:

1 / 86,400 (seconds in a day) * (a specific time value expressed in seconds)

Regular time	Serial time
01:00:00	0.04166666666667 (1 hour, 1/24th of a 24-hour day)
08:00:00	0.3333333 (one third of a 24-hour day)
12:00:00	0.5000000 (half of a 24-hour day)
17:54:30	0.74618055555556 (17 hours, 54 minutes, 30 seconds)
18:00:00	0.7500000 (three quarters of a 24-hour day)

Analytics serial dates compared to Excel serial dates

Analytics serial dates are similar to Microsoft Excel serial dates. You should be aware of one key point of similarity and one key point of difference. The two points are unrelated.

Point of similarity

Both Analytics and Excel treat the year 1900 as a leap year, with 366 days. Although 1900 was not in fact a leap year, Excel treated it as one in order to maintain compatibility with Lotus 1-2-3.

Point of difference

Analytics serial dates are offset from Excel serial dates by one day. In Excel, 01 January 1900 has a serial date of '1'. In Analytics, 01 January 1900 is not counted, and 02 January 1900 has a serial date of '1'.

Converting serial datetimes to regular datetime values

Three conversion functions allow you to convert serial datetimes to regular datetime values with a Datetime data type:

• STOD( ) – “Serial to Date”
• STODT( ) – “Serial to Datetime”
• STOT( ) – “Serial to Time”

You can convert serial datetimes to make results of some datetime expressions more human-readable, or to convert a Numeric serial datetime value to a Datetime data type for use in another expression that requires a Datetime data type.

The table below shows examples of the three functions.

Expression	Results
STOD(42004)	01 Jan 2015
STODT(42004.5000000)	01 Jan 2015 12:00:00
STOT(0.7500000)	18:00:00
STODT(42004.74618055555556)	01 Jan 2015 17:54:30
STOT(`T173000` - `T093000`)	08:00:00

Converting regular datetime values to serial datetimes

Normally, there is no need to convert regular datetime values to serial datetimes. Serial datetimes are used internally by Analytics for datetime storage and computation.

If you do want to see the serial datetime value that corresponds to a regular datetime, you can use the following methods:

Regular datetime value	Conversion expression	Corresponding serial datetime
01 Jan 2015	`20150101`-`19000101`	42004
17:54:30	1.0000000000*((HOUR(`t175430`)*3600)+(MINUTE(`t175430`)*60)+SECOND(`t175430`))/86400	0.7461805556