CODEWITHSUNDEEP
sqlcoldfusionfirebird
Anthony Doherty
Anthony Doherty

Reputation: 111

Convert DOUBLE column to TIMESTAMP in Firebird database

I have a Firebird database that saves the datetime field as a DOUBLE. I have created a ColdFusion datasource connection, so I can query the data remotely. While the rest of the data is being returned correctly, the datetime field is unreadable. I have tried using CAST and CONVERT to no avail. How can I convert this to a timestamp?

An example of the data stored is: 43016.988360

Upvotes: 2

Views: 3575

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109024

You can't just convert a DOUBLE PRECISION to a TIMESTAMP, not without explicitly defining how you want it mapped and writing that conversion yourself (or hoping there is an existing third-party UDF that does this for you).

A TIMESTAMP in Firebird is a date + time represented as an 8 byte value, where the date range is from January 1, 1 a.d. to December 31, 9999 a.d. and the time range is 00:00 to 23:59.9999 (so, 100 microsecond precision).

A DOUBLE PRECISION is - usually - the wrong type for storing date and time information, and as you haven't provided how that double value should be interpreted, we can't help you other than saying: there is no default method in Firebird to do this.

Based on the comments below, it looks like the value is a ColdFusion date value stored as double precision with the number of days since December 30th 1899, see also why is ColdFusion's Epoch Time Dec 30, 1899?. If this is really the case, then you can use the following for conversion to a TIMESTAMP:

select timestamp'1899-12-30 00:00' + 43016.988360 from rdb$database

Which will yield the value 2017-10-08 23:43:14.304. Using the value 43182.4931754 from the comments will yield 2018-03-23 11:50:10.354. That is a millisecond off from your expectation, but that might be a rounding/presentation issue, eg I get the exact expected date if I use 43182.49317539 instead.

I would strongly suggest you carefully test this with known values.

Upvotes: 5

Related Questions