Legacy datalogging allowed for datalogging to SQL where the timestamp was stored in the Timestamp column and optionally, could also be stored in a TimestampStr column. Customers who do not enable the logging of timestamps to the TimestampStr column now require to convert the Timestamp value to a standard date/time format. See sample of table with data below.

Exporting this data to Excel or copying it into a sheet such that it can be viewed in a standard date and time format the following formula will be required.

The formula to be used is “=(( *cellref* /1000)-9435304800)/(24*60*60)” where *cellref* is the cell containing the Timestamp value

**Background**

The SQL Timestamp column is a 64 bit value millisecond UCT timestamp value. A value of 0 represents 1 Jan 1601 00:00:00. An Excel date is the number of days (fractional portion is the time of day) where a value of 0 represents 31 Dec 1899 00:00:00.

Let’s assume that your SQL record has a timestamp of | 13341325280016.00 |
---|---|

We first convert it to seconds by dividing by 1000 | 13341325280 |

We then subtract a constant of 9435304800 (see note below) to convert to Excel base date | 3906020480 |

We then convert from a seconds value to a days value by dividing by (246060) |
45208.57037 |

We finally format the result as a custom date time cell in the format yyyy/mm/dd hh:mm::ss | 2023/10/09 13:41:20 |

It is important to note that the constant of 9435304800 takes into account the conversion to SA local time. To convert to UCT time … use the constant 9435312000