mysql datetime or int?

do you prefer to use a datetime or int field for your dates? I had always used int's but a buddy of mine uses datetime and then uses strtotime() to convert it.

what do you do?

I use INT( 11 ) as the column and UNIX_TIMESTAMP() as value...

does anyone use datetime?

I use date personally. That way I can make date comparisions directly in my select statments and also when you do inserts it prevents any funkyness if for example you try to insert a date that doesn't exist you will get an error. I come from a SQL/400 background and I assume it is the same for MySQL.

I guess my reason for asking is, what are the differences? why would you use INT or DATETIME or DATE over the other?

Ok INT:
It is for integer and is simply a number; thus you can't do date comparions with it (well you can but you would have to write a complex function to copare dates); furthermore, when you enter a date as an INT, SQL won't know that you are trying to enter a month 13 (there is no such thing a Decembuary and won't give you an error.

DATETIME:
Same as date really, except it also stores the time. Do you need to know the time also? If not then use Date; why store unnessary information and if you have lots of records, it will add to your databases size, so why waste space?

DATE:
If it's a date, use date. Simple as that. Easy to make comparisons you can't enter a month that doesn't exisit without getting an error. This is really important if a user is submitting a form for a delivery for example and you give them three drop down menus with options (year, month, day)... if the user selects June 31 they will get an error - If you used INT, everything would go into the datebase ok and you would have to figure out a way to deliever the customer product on June 31... good luck with that =)

Hope this helps. Let me know.

ahh ok cool,...good info.

Unquick summed it up pretty well... I don't have much to add besides the fact that you really should be using a date field if your data is date.

You should be using a timestamp if you want to have the exact date and time updated when you make any action on that record.
These are updated automatically.

As a matter of fact, if I were going to use anything other than a date it would be a character field as opposed to an int() field.

At least there are simple functions to bring a date from character to string easily.

Actually, I have an example.

Say you wanted to store this format of date as an int
mm-dd-yyyy

That would never work as int even if you took away the "-"
mmddyyyy would not work as

01022003 because an int field would strip the leading zero and you would be left with this crap:

1022003


You could get away with this if your date is stored as
yyyymmdd, but that just is not always the case.

hi
i have in my table the date formated with datetime like '2005-10-08 09:53:32'

now i need the data in another database formated as int(11) like 1128881048


how can i do this? has anyone a convert script or something else? i need your help


hardlocke

Use the function UNIX_TIMESTAMP(), it converts a date column to a unix timestamp (nr of seconds since 1970-01-01 00:00:00), which is what you want...

See here for details.

I use date / datetime...cause that is the correct way of doing it in my mind... :P

And you never know when you might feel the need to use that particular information onwards in any development of the site.

What he said....

Wow, a two year old thread.

Why would one use an Int field?
I've always used DATE or DATETIME
UNIXTIMESTAMP make sense as well, but I would never declare a plain old INT.

Quoted for Truth