Features, discussions, tips, tricks, questions, problems and feedback

Converting datalogs SQL timestamp to Excel dates

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)/(246060)” 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

1 Like