[FLOW3-general] DateTime limited to Unix timestamp?

[Michael Sauter]

On 02.07.10 20:51, Martin Kutschker wrote:
> Am 01.07.2010 23:54, schrieb Michael Sauter:
>>
>> Could you explain that a bit? Which cases exist where storing timestamps (with an additional
>> timezone) is not enough?
>
> With an added TZ field it's probably "enough" for many cases. But I fail to see what is so great
> about unix timestamps.
>
>>> For me a storage format of yyymmddThhmmss is much more readable than a timestamp and you can get the
>>> parts with simple string operations.
>>
>> Does storage need to be readable? I think it's more important to be readable by computers, not
>> humans.
>
> Of course it doesn't have to be readable per se, but it helps a lot when you have to wade through
> raw SQL data a lot.
>
> When you use DATETIME or a date formatted CHAR you can do all kind of queries directly (like
> selecting records from a given date) and intuitively. You cannot do that with unix timestamps.
>
> IMHO they are only handy for simple logging tables, but even then they have drawbacks.
>
>>> But we left out the obvious choice: an SQL date field. It has all kinds of bells and whistles (like
>>> arithmetic) and is built for the job.
>>
>> AFAIK SQL databases differ on their date type handling. MySQL for example has (among others)
>> TIMESTAMP  and DATETIME, I guess you are referring to the latter?
>
> Of course.
>
>> I think it's also important to keep an eye on non-SQL databases (I know, not yet there in FLOW3, but
>> who knows?). Obviously the interface of interacting with a "backend" should be the same. But if you
>> can't rely on date functions for all backends (even SQL databases differ concerning date functions),
>> it's getting harder to have the same interface. Dealing with a relatively simple type like integer
>> is easier.
>
> How the data is stored in the BE shouldn't worry you in the client code. If all you get is a
> DateTime object why worry if it's stored as a unix timestamp or as a SQL DATETIME field?

That is more or less what I'm saying ;) The client code should be the 
same, no matter the BE. So FLOW3 needs to be sure that all functionality 
it offers should work with (almost) any databases. Say you want to use a 
SQL DATETIME field to store dates, because it is a good way to store 
dates (I agree, better than timestamps). But why exactly is it better? 
Mainly because MySQL offers you some functionality that makes 
handling/selecting much easier. Now if FLOW3 wants to support these 
operations, it needs to support it for all databases. But not every 
(even SQL database) has this ... so how should FLOW3 do this? Load every 
record and check? IMHO a SQL DATETIME field is not better than a simple 
string field without the date operations.

That's why I think easy (although less optimal) storage solutions work 
better in this case. So in my eyes the options are down to timestamp or 
string. Both need to be in UTC to make comparisons work correctly (if 
you store the timezone with a string and do a string comparison, you can 
get wrong results). This means timezone and date/time need to be 2 
different fields anyway. Now we're down to timestamp/string for 
"date/time without timezone". So all in all, not much difference here. 
Especially if you work with DateTime objects in PHP - it's likely you're 
going to create a DateTime object, get the timestamp or string 
representation of it and compare that to the DB ...

But, in the end, date strings might really be a better choice cause as 
you said, there are probably no disadvantages compared to using 
timestamps (but some advantages like better readability, handling of 
historic dates). You convinced me ;)

~michael