Serial datetimes
Analytics uses serial datetimes to store dates, datetimes, and times, and perform datetime calculations.
You may encounter a serial datetime value when working with datetime expressions. For example, subtraction operations that involve only date, datetime, or time values return results in the form of a serial datetime.
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. For example:
- 42004
- 42004.50000
- 0.75000
- 42004.74618055555556
The portion before the decimal point is the date, and the portion after the decimal point is the time.
The date portion
The date portion is the number of days that have elapsed since 01 January 1900. Therefore, 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. So, 0.5 is equivalent to 12 hours, 0.75 is equivalent to 18 hours, 0.04167 is equivalent to 1 hour, and so on.
Equivalent regular datetime values
The serial datetimes above are equivalent to the following regular datetime values:
- 42004 = 01 January 2015
- 42004.50000 = 01 January 2015 12:00:00
- 0.75000 = 18:00:00
- 42004.74618055555556 = 01 January 2015 17:54:30
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.50000) |
01 Jan 2015 12:00:00 |
|
STOT(0.75000) |
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 |
Analytics 14.1 Help