datetime vs int(12)

[tbakerisageek]

AAAAhhhhhhhh..... That's why I asked. I wasn't aware that there was such function in MySQL

[longneck]

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"

in PHP, the (12) and (10) is completely ignored. AFAIK, the only program that honors those formatting rules in the mysql CLI. the mysql manual calls this value "length" and it has nothing to do with the storage requirements or the maximum allowable values.

INT takes 4 bytes. the allowed values are -2147483648 to 2147483647, or 0 to 4294967295 for UNSIGNED.

see http://dev.mysql.com/doc/refman/5.0/...ric-types.html and http://dev.mysql.com/doc/refman/5.0/...uirements.html

experience must be moderated by knowledge of the documentation, and effective use of documentation is difficult without experience.

[longneck]

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

ah, i think i summed it up quite nicely in that thread:

datetime. you will then be able to use the built-in mysql functions to do date calculations. but if the only think you ever need to do with the columns is compare using =, > or < then unix timestamps are ok.

my personal preference is to use datetime columns because you can do everything you can do with unix timestamps, plus more without the overhead of converting them first.

plus when you're browsing your database with a query tool, you can actually read your dates!

[tbakerisageek]

plus when you're browsing your database with a query tool, you can actually read your dates!

I have to give you credit on that one... I revert to using a unix timestamp calculator extension for firefox all the time.

[longneck]

you could also just write your queries with from_unixtime()

[felgall]

The more work you can offload from PHP into the SQL the more efficiently your program will run. The worst code the database will use to do what you ask is the same as the PHP you would write yourself and often the SQL can find a more efficient way of doing it during the actual search and retrieval process.

[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

[r937]

here's another reason: store birthdays before 1970, or due dates after 2038

[tbakerisageek]

I see the validity there, but I could write a query to fit each of the given examples that would return only the data I want from the table. Not retrieve all of it and then parse only what I want.

[r937]

okay, another reason: when browsing data through a front-end like phpmyadmin, you can't tell what date the stupid integer is just by looking at it

that should be your tie-breaker right there

[tbakerisageek]

I feel like there are a lack of people on my side!!!!!

If someone can post an example of how to do a diff between dates in a functional way, I think I'm convinced to change up...

[trib4lmaniac]

If someone can post an example of how to do a diff between dates in a functional way, I think I'm convinced to change up...

The only "diff" that you can easily do with timestamps is a simple subtraction of two such values, resulting in the number of seconds between them. So I am assuming you are asking how to do that with DATETIME values.

SELECT TIMESTAMPDIFF(SECOND, '2007-10-08 11:30', '2007-10-11 18:45');
-- Returns 285300

Of course, as you are using DATETIMEs now, you can even be flexible about what you get in return.

SELECT TIMESTAMPDIFF(HOUR, '2007-10-08 11:30', '2007-10-11 18:45');
-- Returns 79

And if all that has been mentioned in this thread already doesn't convince you that DATETIMEs are worthy of our usage, then remember that you can always use UNIX_TIMESTAMP should you ever have need to revert to your old ways (which you won't ).

SELECT UNIX_TIMESTAMP('2007-10-08 11:30')
-- Returns 1191839400

Of course, this naturally implies that you can perform the original calculation (difference in seconds between two times) like this:

SELECT UNIX_TIMESTAMP('2007-10-11 18:45') - UNIX_TIMESTAMP('2007-10-08 11:30')
-- Returns 285300

[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

[monkey56657]

When you get down to it even with the datetime format behind the scenes MySQL is storing it as a number anyway right ? The only advantage of using the date datatype is for easy changing and viewing without any conversion if you ask me...so yeah I use and will always use the INT(10) or just INT(11) if i get too lazy to type 10 into the box on phpmyadmin

[r937]

or just INT(11) if i get too lazy to type 10 into the box on phpmyadmin

since you are lazy, why not just type INT

[TheRedDevil]

The more work you can offload from PHP into the SQL the more efficiently your program will run. The worst code the database will use to do what you ask is the same as the PHP you would write yourself and often the SQL can find a more efficient way of doing it during the actual search and retrieval process.

This is not always true. It is cheaper to scale php than mysql. So depending on your application it can be both cheaper and preferred to let php do some more work.

Personally I use both. DATETIME when the result is not used only by the script and it makes more sence to use it compared to a timestamp. Sometimes you have to use it, forexample if your going outside of the timestamps range.

That said, when considering application performance and load required its sometimes wise to use timestamp. Especially if these values are only used within the script itself to do calculations and never displayed.
Why add an additional convertion if its not needed?

[Jake Arkinstall]

What I tend to do is store the strings for all of the mysql queries I'm likely to use in an application.
Because I do this, I don't want to have to convert it all from a timestamp to a date when it comes to fetching a date/time result, therefore I use DateTime. It means that I can store repeditive queries, and upon calling them I imediately get the results I'm after - without conversions.

Beat that with a point stick using a timestamp