[TYPO3-dev] It works! Storing non-UNIX dates

[Ries van Twisk]

Ernesto Baschny [cron IT] wrote:
> Hi David,
>
> David Bruehlmeier schrieb am 01.06.2006 21:51:
>
>   
>> sometimes, there is the need to be able to store dates outside of the
>> UNIX-timerange, e.g. for birthdates. This has been discussed a few times
>> before, and I now implemented a proof-of-concept extension using the
>> ADOdb Date Library. ADOdb is shipped as a system-extension with every
>> TYPO3 implementation.
>>     
>
> Please note that this is only being shipped since 4.0.
>
>   
>> It works using two hooks:
>> 1. In TCE-Main, the date entered is converted to an int64 value before
>> it is stored in the database.
>> 2. In TCE-Forms, the date is converted back when loaded from the
>> database before displayed in the Backend.
>>     
>
> The second hook is only available in TYPO3 4.0.
>
>   
>> If anyone is interested, here's the test-extension:
>> http://www.feg-duedingen-freiburg.ch/fileadmin/spec/T3X_test_ext_dates-0_0_0-z-200606012135.t3x
>>     
>
> I took a look and I think we could implement something like that in
> TYPO3 core directly and would like to rise my interest in getting this
> done with your help, David. This has been requested several times now,
> and even I have stumbled over the problem yesterday.
>
> Before I discuss this with the core, I would like some more input from
> this list.
>
> Here's a first list of basic facts, ideas, limitations, brainstorming:
>
> AdoDB time functions are very straightforward [1]. They provide working
> replacements for PHP native "unixtime" based time functions:
>
> 	getdate()  with  adodb_getdate()
> 	date()     with  adodb_date()
> 	gmdate()   with  adodb_gmdate()
> 	mktime()   with  adodb_mktime()
> 	gmmktime() with  adodb_gmmktime()
> 	strftime() with  adodb_strftime()
> 	gmstrftime() with  adodb_gmstrftime()
>
> When generating timestamps earlier than 1970, the integer will simply
> become negative. This works even on Windows (I've just tested it), as
> it's implemented in PHP and not using native system calls.
>
> The nice thing about having integers to represent dates is that they are
> very easy to sort, easy to calculate with, etc. Another nice thing is
> that all dates we already have in our TYPO3-installations will
> automagically "work" with this new setup, as the positive integer range
> is exactly the same.
>   

> If we store the signed integer back to MySQL, we have to observe the
> DB-limitations [2]: a "signed int" (32bits) field goes from -2147483648
> to 2147483647, which in AdoDB-Time means dates from 1901-12-13 to
> 2038-01-19. This already seems to cover most cases (e.g. "birthday").
> For someone storing historical or geneological information, this might
> not be enough, and the next step would be a "signed bigint" (64bits)
> which then is more or less "limitless" in the date range. But then, it
> might not be cross-compliant with other DB-systems that might not have
> this kind of integer type (from mysql docs: "As an extension to the SQL
> standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and
> BIGINT.").
>
> The Kickstarter is generating a "INT(11) DEFAULT '0' NOT NULL" field out
> of a 'date' field. This already seems ok ("SIGNED") for negative
> unixtime storage.
>
> The t3lib/jsfunc.evalfield.js will also need some changes, but I just
> tested and it seems that JS also supports (horray!) negative unixtimes
> in its getTime() method (tested in FF, IE6 and Opera7). I could not find
> any documentation on limits for this particular JavaScript method.
>
> So I can imagine two ways of implementing this.
>
> 1) simply extending the 'date' TCA eval to handle negative dates.
>
> The "pros":
> - All tables from extensions will automatically handle such dates
> The "cons":
> - Not all current 'date' fields in TYPO3's SQL-definitions are signed
> and not all 'date' fields would really require such a functionality
> (e.g. starttime/endtime).
> - The extension that uses that date would need to call adodb-functions
> to get them displayed correctly if they become negative.
>
> 2) have a new additional type 'date_extended' in TCA eval that implement
> this new type.
> The "pros":
> - Having the author explicitly having to set this makes him aware of the
> SQL requirements to store this info and to work with such dates later
> The "cons":
> - Might be difficult to provide "backwards compatible" non-date_extended
> field if the extension is installed in older TYPO3 environments. I can
> imagine an eval 'date,date_extended': the 'date' will be used in older
> environments (date_extended being ignored) and on newer installs the
> 'date_extended' will be used (and 'date' will be ignored). Don't know if
> its that easy to implement that, thou.
>
>
> Cheers,
> Ernesto
>
>   
Ernesto,

I am not much of a typo3 core guru, would it be possible to extend the 
kickstarter
with a real datatime instead of a integer. The real negative part of 
storing integer numbers
is that you cannot put a timezone with the datatime. This is a huge 
negative if you want
to create world wide events for example.
I do think that all serious database engines, even mysql for a change 
can store this information
the correct way. I don't say we need to change in feature all int(11) to 
datatime stamp,
but it would be great if we can extend the kickstarter and have a option 
for a real datatime
storage instead of integer (in any form).

You mention two 'nice' things about integer, easy to calculate and to sort.
1) Sorting with dattime stamps is easy aswell in SQL
2) Calculating with date integers is extremely differcult if you want to 
calculate
with timezone in mind, functions do exist that can do that for you. With 
integers
you need to write all functions yourself to handle that.
consider a international conference call and I want to show on each 
computer (japan, holland, USA)
the the left to the actual call. This is difficult when working with 
just integers.


again, I don't say we need to change current method, and I would love to 
see the current int method extended.
I would just like to see typo3 doing real data/timestamps possible in 
extensions. I know the bigger companies
are asking for such a thing (and want to move to a other RDBM then mysql 
aswell).

I am a big fan of method 2:  date_extended 
For backwards compatibility reasons I think we have that covered,
when I install extensions now it simply says I need version xxx.

When I would make any extension now, the closest thing I can test on is 
typo3 3.8.x
But I would develop and test it around 4.0. I think for me it's fair to 
say non-tested
on typo3 version 3.x so I don't see the date-extended as a con.
I assume with the new type is a real date/time field instead of an integer.

Let's move away from typical mysql solutions used back in the mysql 3.x !!!!

my point of view and suggestion.

Ries



-- 
Ries van Twisk
Freelance Typo3 Developer
=== Private:
email: ries at vantwisk.nl
web:   http://www.rvantwisk.nl/freelance-typo3.html
skype: callto://r.vantwisk
=== Work:
email: ries at livetravelguides.com
web:   http://www.livetravelguides.com