datetime vs int(12)

[wonshikee]

for storing dates in a mysql db, which is the recommended method and why?

[Jake Arkinstall]

I think DateTime is the best to use. It is made for displaying and ordering time correctly, after all.

Once it's retrieved from the database, you can simply use strtotime() to convert it back to an ordinary timestamp, which you can then use the date() function to turn it into a date/time, formatted to your choice.

[triexa]

I personally use INT for storing times

[tbakerisageek]

I like to use INT(10) to store the unix timestamp that can be generated using time() All of the time comparrisons I do, I like to use the timestamp itself.

[stereofrog]

for storing dates in a mysql db, which is the recommended method and why?

DATE or DATETIME, because that's what they are for. This is the same reason why you wear your shoes on your feet and not on your head.

[tbakerisageek]

DATE or DATETIME, because that's what they are for. This is the same reason why you wear your shoes on your feet and not on your head.

Writing code in PHP, the output of time() is the easiest way to handle timestamps. While PHP and MySQL are great together for almost everything, the difference in how they handle timestamps is a big hurdle. I do my coding and diff'ing in PHP not on the MySQL end. This is my reason for doing the int(10), otherwise, you have to convert the DateTime into something that PHP can function with every time you pick a date and time from a database.

This is why I love PHP/MySQL. There are limitless ways to do what ever it is that you want. The only "Best" way to do something is how you decide to once you know the ramifications of each way you are considering doing it.

Nice analogy though...

[stereofrog]

Yes, of course, you have the full right to wear your shoes on your head, if you want

Seriously, what exactly are the advantages of using time() and storing timestamps in DB (except that it is "easy")? Can you provide a real-life example?

[wonshikee]

Yes, of course, you have the full right to wear your shoes on your head, if you want

Seriously, what exactly are the advantages of using time() and storing timestamps in DB (except that it is "easy")? Can you provide a real-life example?

What is a real-life example that shows datetime is better to use?

Memory wise, is datetime <= to an int?

[tbakerisageek]

What is a real-life example that shows datetime is better to use?

Memory wise, is datetime <= to an int?

I'm waiting on what he comes back with too.

BTW the OP was wrong, it doesn't have to be int(12) it only needs to be int(10)... two bytes shorter per record for 10,000 records or more adds up quickly!!!

[longneck]

Memory wise, is datetime <= to an int?

• DATE is 3 bytes (supported range '1000-01-01' to '9999-12-31')
• DATETIME is 8 bytes (supported range '1000-01-01 00:00:00' to '9999-12-31 23:59:59')
• INT is 4 bytes (supported range when unsigned '1970-01-01 00:00:01' UTC to partway through the year 2038)

BTW the OP was wrong, it doesn't have to be int(12) it only needs to be int(10)... two bytes shorter per record for 10,000 records or more adds up quickly!!!

sorry, you're wrong. the (12) and (10) only affect display formatting and is completely unrelated to storage requirements. an INT is only 4 bytes.

[tbakerisageek]

DATE is 3 bytes (supported range '1000-01-01' to '9999-12-31')
DATETIME is 8 bytes (supported range '1000-01-01 00:00:00' to '9999-12-31 23:59:59')
INT is 4 bytes (supported range when unsigned '1970-01-01 00:00:01' UTC to partway through the year 2038)

sorry, you're wrong. the (12) and (10) only affect display formatting and is completely unrelated to storage requirements. an INT is only 4 bytes.

I guess I'm not understanding what you mean by "the (12) and (10) only affect display formatting and is completely unrelated to storage requirements"

I've never expanded my INT's to 12 from 10 and I've never had a problem storing a unix timestamp in the format of "1098932400" which == "October 27, 2004 at 10:00:00 PM" stored as 10 sequential characters (in timestamp format, which is what I store)

[r937]

it doesn't have to be int(12) it only needs to be int(10)... two bytes shorter per record for 10,000 records or more adds up quickly!!!

actually, no, that's wrong

INT(10) is the same as INT(1) is the same as INT(937) -- they're all just 4 bytes

[r937]

What is a real-life example that shows datetime is better to use?

here are three

• retrieve all sales for the current month, up till today at noon
• retrieve all sales which took place on a tuesday
• find all birthdays in the next 20 days

make sure that you return only the desired rows, i.e. do not return all rows in the table to php in order to decide which rows you want

[Ohidur]

Best to use Date or Datetime. This data type is to store datetime related data so why u want to store into int(12).

Thanks
Ohid
------------------------------
www.bdwebservice.com - Manual Directory Submission Service, Article Submission Service, Link Building Service.

[Jake Arkinstall]

Hi ohid, welcome to the forums.

[tbakerisageek]

Hi ohid, welcome to the forums.

Gotta give us your input on the topic too...

[tbakerisageek]

I could never see any benefit to using the DATETIME format in MySQL. I may have lacked a mentor to show me the way with that format.

A few examples... As a little fun, I'd love to see your rebuttal using the DATETIME format.

<background info>
I've put timers on sites that display how long it has been since somthing occured or how long it will be until it occurs
</background info>

$now = time();
$RedSoxWonTheWorldSeries = '1098932400';  //had to look it up... and convert it...

//How long has it been since the Sox won the world series?
$itsBeenAWhile = $now - $RedSoxWonTheWorldSeries;

//We now have the number of seconds that it has been since the Sox won the World Series

//And Minutes, hours, days, weeks...
$minutes = $itsBeenAWhile * 60;
$hours = $minutes * 60;
$days = $hours * 24;
$weeks = $days * 7;


//   Of course the timestamp for when the Sox, or any item for that 
//   matter could be stored in the DB.  I'm not retrieving it here because the 
//   debate is not over how you'd retrieve it. 

Next Example...

<background info>
Selecting all events that have happened between certain dates/times
</background info>

//  Use your favorite method of selecting a date and time 
//  on a previous page and convert them to unix timestamps using your 
//  favorite PHP method.

//Store the result in $startTime and $endTime
$startTime = strtotime(''); //you'd convert your $_POST or $_GET data here
$endTime = strtotime('');  //and here

//Compile query
$query = 'SELECT * FROM Table WHERE timestamp > '.$startTime.' AND timestamp < '.$endTime;

//  The returned results will be only the times that happened 
//  between the date and time you specify for the start time and the end time. 

[stereofrog]

I could never see any benefit to using the DATETIME format in MySQL. I may have lacked a mentor to show me the way with that format.

The conclusive advantages are the ability to query date parts and to perform date arithmetics, e.g.

select * from users where year(birthdate) < 1989
update orders set expiry_date = date_add(now(), interval 2 month)

In fact, mysql date module is far more powerful than that of php (see http://dev.mysql.com/doc/refman/5.0/...unctions.html), so there's no reason to rely on the latter when working with mysql.

I've put timers on sites that display how long it has been since somthing occured or how long it will be until it occurs

select datediff(date1, date2)

Selecting all events that have happened between certain dates/times

$db->query("SELECT ... where event_date between 
   str_to_date(?, '%m/%d/%Y') and
   str_to_date(?, '%m/%d/%Y')
", $_GET['start_date'], $_GET['end_date']); 

[wonshikee]

So I guess my question is a pretty valid one. I've always preferred to simply store the time() or UNIX_TIMESTAMP(), because I believed forcing the db to output time in a specific format is extra work for db that php could do, and also pulling it out and using a function like strtotime just to get a timestamp that i'm going to redump back into another function date() when it could be brought straight in from db seemed like killing 1 bird with 2 stones.

[longneck]

while both are perfectly valid approaches, DATE and DATETIME have the distinct advantage of being usable with the various date and time functions in mysql. if you use unix timestamps (i.e., the INT you keep referring to) then you have to convert that data before you can use any of these functions.

tbakerisageek- yes, you may have to convert the dates and times as you extract them from the database, bu tin general i find that i only ever need 2 conversions for entire applications that i can just copy-and-paste in to the relevant queries: one to convert DATE, and the other to convert DATETIME.

[Jake Arkinstall]

Gotta give us your input on the topic too...

I did, I was the first to answer.

Memory wise, is datetime <= to an int?

Actually, I think it is. Otherwise, there would be no need for it as a field type.

I've noticed more and more recently that every feature of mysql is thought through thoroughly (to many "th"s!). There are alot of benefits to using the default ways.

I feel a metapor coming on... If you were given legs to run with, why run on your hands?

[tbakerisageek]

I did, I was the first to answer.


Actually, I think it is. Otherwise, there would be no need for it as a field type.

I've noticed more and more recently that every feature of mysql is thought through thoroughly (to many "th"s!). There are alot of benefits to using the default ways.

Sorry, must have missed the second post, or not realized it was by you by the time I read your post I commented on.

It appears to me that one must select a location to process their dates. Either in PHP or in MySQL.

I'm more comfortable using PHP to do any data processing as I tend just to use MySQL for a data-repository. Maybe it's that I'm not familiar enough with what MySQL has to offer for data processing features like you're reccomending or it's just that I learned how to do what I need in PHP first.

@Arkinstall, Care to take stereofrog's place in the debate and post real-world code to the MySQL DATE/DATETIME format for us?

[wonshikee]

I feel a metapor coming on... If you were given legs to run with, why run on your hands?

I'm sure there are plenty of examples of creators giving you features you can use that ended up being bad idea.

Not that datetime is a bad features, but making the argument that just cuz they say to use it, you should use it is a fallacy.

I could think of an easy counter argument - they offered you register_globals, so many developers used it - and it turned out to be a bad idea right?

[tbakerisageek]

I don't mean to indicate that it is a bad option either. I was asked what I do, and why.

I'm not familiar enough with the benefit of using a DATE or DATETIME format in MySQL so I choose not to use it as I have something else that works just as well for me.

and Yes, Register globals is a bad idea!

[Jake Arkinstall]

Yes - however I'm sure DateTime has no security problems

Anyway, I cast you all back to a thread started 2 years (just 3 days out) ago, when a similar conversation was to be discussed (Longneck was there!):
http://www.sitepoint.com/forums/showthread.php?t=306686

(Yes, that's 20,1035 threads in two years! Go SITEPOINT!)