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)
Tip
Another way of thinking of a serial time value is that it represents a percentage of a 24-hour day.
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) |
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:
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 |