[FLOW3-general] DateTime limited to Unix timestamp?

[Michael Feher]

> As already pointed out (also by Karsten himself), there certainly is a
> better solution to storing date/time. As I suggested earlier, I would
> use strings to solve the range problem (thus making sure that what you
> store is what you get back). Then you can improve on this and store it
> in a specific DATETIME field where possible, for performance reasons,
> you're right.

In the first place I thought why would you want to convert a DateTime 
object into a String as it is rather a Number than a String but after 
reconsidering I am now convinced that the Sting is the best we can do as 
it allows to have a generic handling for all SQL databases.

When you write SQL queries including DateTime you will write something like:

"SELECT * FROM Table WHERE DateTime > '2010-07-23 17:24:39';"

This means that the DateTime object has to be converted to a string 
anyway. The difference between SQLite and MySQL is that MySQL will 
interpret the String as an DateTime object and will take advantage of 
the internal optimization.

I have done some additional research on the DateTime Objects in 
different Databases and here are my findings:

MySQL: Does not support timezone in DATETIME objects. Therefore the 
timezone would have to be stored in a separate field. Further more is 
the supported range '1000-01-01 00:00:00' to '9999-12-31 23:59:59' which 
is less than the PHP DateTime Object. However I think this is still 
better than timestamp.

PostgreSQL: Supports TIMESTAMP with or without timezone. Supported range 
4713BC - 294276AD. Values are stored internally in UTC.

MS SQL: I could not find the documentation easily on the web and I did 
not want to spend hours to find it. So if anybody has some info about it 
please post it here.

ORACLE: Supports TIMESTAMP with or without time zone. Supported range 
4713BC - 9999AD.

Summing this up I would propose for the abstract SQL backend to use UTC 
DateTime values as String without a timezone and have an extra field 
which holds the timezone. This will work basically on all SQL databases.

The SQLite backand which inherits form the abstract SQL backend will 
implement the TABLE CREATE statements which creates a TEXT field where 
the MySQL backend will create a DATETIME field.

Other Database backends may even go further and overwrite the functions 
from the abstract SQL backend to support TIMESTAMPS WITH TIMEZONE but I 
don't see a reason to do this now.

Michael