Page 1 of 1

[SOLVED] wheel of time()

Posted: Thu Sep 16, 2004 8:48 am
by jakobdoppler
Hi

As PHP offers functions to return unix timestamps, i wonder what would be the best Date & Time format for a database (mySql) <-> PHP communication.

Shall I use PHPs unix timestamp in seconds, that is represented by an integer and therefore is running out of memory within the next couple of decades ;-) (btw. does Unix by that time have a similar problem to the Y2K bug ? ) and use a <integer> field type in mySql.

On the contrary there are special mySqls time formats like <timestamp> or <datetime>, that are displayed in Y-M-D h:m:s, but need a little more converting in PHP.

So which is the most proper way, to work with timing information ? Any comment much appreciated , as always... _yak

Posted: Thu Sep 16, 2004 8:52 am
by dethron
i prefer to write some convertion functions and use mysql's <timestamp> and <datetime>
doing so, you have much power on db than the code.

Posted: Thu Sep 16, 2004 9:51 am
by pickle
I always use UNIX timestamps for storing dates. While it does seem that there will be a problem in 2038, I'd be that by that time, the format of the timestamp is extended a few numbers, to carry on to the future - so I wouldn't worry about that.

On the other hand, you can store times as a MySQL timestamp. I believe MySQL has a function that will automatically convert it's timestamp into a UNIX timestamp for you - no additional PHP required. There's a link to the appropriate page from either the [php_man]date[/php_man] or [php_man]time[/php_man] manual page.

Posted: Thu Sep 16, 2004 9:56 am
by timvw
my favorites from http://dev.mysql.com/doc/mysql/en/Date_ ... tions.html

DATE_FORMAT
FROM_UNIXTIME
UNIX_TIMESTAMP

Posted: Thu Sep 16, 2004 11:22 am
by feyd
offtopic-ish:

as long as applications are using time_t instead of int as their timestamp type, they'll be fine. time_t will be updated at some point to a 64 or 128-bit number. int will also probably move to a 64-bit number, but that's not definite. The epoch could be altered too.. at any rate, it'll probably be just a recompile for most applications.

Posted: Fri Sep 17, 2004 2:10 am
by jakobdoppler
Hej, thx a lot ! Finally improved my time managment. :D
never knew these fancy mySql functions do exist ...