[FLOW3-general] DateTime limited to Unix timestamp?

[Michael Sauter]

Hi,

On 01.07.10 20:50, Martin Kutschker wrote:
> Am 01.07.2010 20:10, schrieb Michael Sauter:
>> Hi,
>>
>> On 01.07.10 19:13, Martin Kutschker wrote:
>>> But that's not the only reason why you want to store date/time objects in a different format.
>>>
>>> I'm not sure if I can store arbitrary objects in the DB in FLOW3 (in Extbase you can not), but let's
>>> assume for now we can then I think there could be different date/time objects that behave like a PHP
>>> DateTime but persiste differently. Or there is a special annotation that let's you choose between
>>> different storage formats.
>>>
>>> timestamp (Unix epoch), eg 1278003973
>>> string (yyymmddThhmmss#oooo), eg 20040212T151921+0000
>>> tzstring (yyymmddThhmmss(oooo)), eg 20040212T151921(0000)
>>>
>>> "string" and "tzstring" differ in the way the time zone is handled. "string" uses a regular
>>> approach, to get the UTC date/time you must add the offset. With "tzstring" the date/time *is* in
>>> utc, the offset denotes only the TZ used when entering the TZ (which makes it possible to sort the
>>> field in the DB.
>>
>> What exactly is the problem here when using a timestamp only? A timestamp is by definition "seconds
>> since 1.1.1970 00:00 in UTC". When you have a timezone attached to your DateTime object, the
>> conversion to a timestamp will take the timezone into account. This means all persisted
>> "dates/times" are in UTC, which is perfectly fine. All you need to do is to store the timezone as
>> well (another property of the model) so you can output your date in the timezone you want. Storing
>> the dates in the same timezone (same format) also enables you to do queries (comparing dates) a lot
>> easier ... the timezone information is pretty useless there, no?
>
> No, it's not useless.
>
>> IMHO timestamps are all you'll need.
>
> Definitely a lack of imagination.

Could you explain that a bit? Which cases exist where storing timestamps 
(with an additional timezone) is not enough? Please note I'm not 
referring to handling date/time (obviously DateTime is better here), but 
to storing it in a DB.

>> Maybe except dates prior to 1970, but it is not that complex to implement your
>> own handling in the model
>
> Actually negative timestamps are allowed (even on Windows), so you can express dates prior to 1970
> as timestamps. In case you don't bother at all about dates before 1970 I have some rough guessing of
> your age ;)

I didn't say I don't bother. Although I didn't know negative timestamps 
work (never had to use them), but hey, all the better.

>> Or maybe you had some other case in mind?
>
> "tzstring" stores the TZ that was used to create the date/time object, but of course you could also
> use a separate DB field for that.
>
> 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. Actually, the way FLOW3 uses SQL 
databases is not at all "readable" anymore (at least not to me) ...

> 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?
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.

~michael