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

[Ernesto Baschny [cron IT]]

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

[1] http://phplens.com/phpeverywhere/adodb_date_library
[2] http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html