Do you store time as unix timestamp or MySQL time datatype?

phproy

Hi fellow developers.

I have been storing time on my DB as Unix timestamp.
It works for me and I think its pretty convenient but I still see others who store time as time datatype. There must be some advantage to it.

Anyone comment?

laserlight

Using the Unix timestamp means that after a certain date (not too far away where 32-bit integers are concerned), you're going to face a Y2K type of problem.
Using the timestamp provided by the database should avoid that, at least in so far as you'll have a Y10K problem instead.

phproy

As far as I know Unix Timestamp will be doing Ok till year 2032(guesstimate).

By then I don't think that internet will be the way it is today. It might be replaced with something more phenomenal. 😃

Which one is more convenient to work with anyway?

jayant

use the one u are comfortable with 🙂

Using the timestamp provided by the database should avoid that, at least in so far as you'll have a Y10K problem instead.

unless one is parsing the date to get the year using php in the wrong way like substr($dt,0,4) instead of substr($dt,0,-10)
then only it will cause the problem, using substr($dt,0,-10) wont cause the problem ever.

when y10k comes, just alter the date field's length by 1 and pre-pad with 0's 🙂

Sxooter

Where are you going to be doing your date maths? If you'll need to do work on dates in the database, then definitely store them as timestamps. If you're going to be doing most of your maths in PHP then unix timestamps.

If you need to work with time zones and all, then definitely do it in the database.

phproy

I will be doing the maths on PHP isntead of MySQL.
I have been doing that.

Is it more conveninent to use MySQL to do the date math?

I would have a form on my site to let people specify the date range to pull record from . e.g. 02/05/05 AND 02/06/05
In PHP it is a little troubleshome to convert the dates to unix timestamp. Is MySQL date math more flexible?

All I know is that PHPbb which is a very powerful forum application is using unix timestamp. So I though timestamp is the way to go then. "monkey see monkey do" 😃

Sxooter

Doing date / time math in the database lets you do things pretty easily, like:

select * from table where datafield between '2005-01-01' and '2001-03-01'

which would require you to do an mktime thing to make your unix timestamps in php and have a query with between '102029203' and '102039500' kind of thing

However, older versions of MySQL didn't do intervals and a few other things all that well, so it's a bit of a toss up.

phproy

And what about timezone handling when using MySQL time? I had spectacular success when using timestamp. 😃

Is it possible to convert MySQL time to Unix timestamp?
I would still like to keep the ability to do math on my PHP script. People would love to eat the cake and keep it at the same time if given the chance you know. 😃

Sxooter

I'm not sure how well MySQL handles time zones, I generally use postgresql, which has quite good time zone support. How would one handle time zones with Unix timestamps, since they don't really have the ability to store an offset?

Wihchever you choose, stick with it.

phproy

I use this when working with unix timestamp:

gmdate("H") + $offset;

😃

jayant

Originally posted by phproy
And what about timezone handling when using MySQL time? I had spectacular success when using timestamp. 😃

Is it possible to convert MySQL time to Unix timestamp?
I would still like to keep the ability to do math on my PHP script. People would love to eat the cake and keep it at the same time if given the chance you know. 😃

MySQL has UNIX_TIMESTAMP() function.