What can bite me if I store a datetime as a float in the database?

Question

What can bite me if I store a datetime as a float in the database? I have a very good reason for doing it so don't complain about that :)

Edit: I was thinking about just storing convert(float, @thedate) in a float column.

Answer 1

Precision loss for one. Lack of resolution is another.

Its a minor issue, but floating point versions IEEE 754 vs VAX Floating Point.

Answer 2

If you're going to store that data as a float, I think you'd be better off storing seconds since epoch as a float than datetime as a float

Answer 3

SQLite uses a time format (one of a few available) with a (64 bit) double float using the integer part for days since an epoch, and the fractional part as fraction of a day. It seems to work well.

See SQLite Date and Time Functions "Format 12 is the Julian day number expressed as a floating point value."

Using Julian Dates 15 decimal digits gets you millisecond precision for several millennia.

According to this Julian Date Converter, JD 9999999.99999 is CE 22666 December 20 11:59:59.1 UT Thursday

Answer 4

What Alan said, plus... the fundamental problem of maintenance; when someone else comes onto the project, and sees the float for datetime in the DB, and tries to do something wrong with it, or tries to refactor it to the proper type, or just spends hours poring over the code to figure out what the heck is going on. The whole problem of maintenance can to some extent be mitigated by extensively documenting what's going on and why you're doing it, which I'd recommend highly.

Answer 5

Actually, as far as I know, MSSQL and Oracle actually do internally store datetimes as floats (as day and fractional days)

select cast(0 as datetime), cast(0.5 as datetime)
1900-01-01 00:00:00.000 1900-01-01 12:00:00.000

Answer 6

Regardless of precision - depending on what number you choose as a start point, you may have trouble with comparisons.

If you are unable to create precise floats for each datetime, you may also have have two datetimes that compare both one way and the other if they resolve to different floats.

Answer 7

You lose some precision. I tested in SQL Server with:

select getdate(), cast(getdate() as float), cast(cast(getdate() as float) as datetime)

You can see if you run this repeatedly that you can lose as much as 4 milliseconds in the conversion. If your database supports a data type like smalldatetime and you only need accuracy to the second, then you can smooth out this difference.

Answer 8

Here is a good article on "Demystifying the SQL Server DATETIME Datatype"

http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

From reading that, it looks like datetime is stored as 2 4-byte ints or you could use binary(8)

as others have said, storing as a float causes you to lose some precision.

Answer 9

What can bite you? Well first float is not an exact datatype and thus should probably never be used for anything that requires precision. Next, float will not automatically reject an incorrect date. Next, if you want to perform any date functions you will first have to convert the data back to a datetime data type which is a waste of server resources.

You say you have a good reason for wanting to do this, but with a clue as to what that might be, I submit that the dates should be stored in the datatype meant to handle them.

Answer 10

A potential disadvantage of using a float would be that you lose the ability to use the system's built-in date manipulation functions. If you're only doing interval calculations, floating-point may be fine, but anything relating to calendars could require some wheel-reinventing.

BTW, it seems Oracle at least uses fixed fields for its internal date representation:

select
  to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as Date_Value,
  dump(sysdate) as Internals
from dual;

DATE_VALUE          INTERNALS                                                   
------------------- ------------------------------------------------------------
2009-05-28 09:51:12 Typ=13 Len=8: 217,7,5,28,9,51,12,0                          
1 row selected