Storing dates in mysql table

[cnapsys]

Hi all,
I have a few questions about storing php timestamps in mysql tables.
What is the best mysql type field for storing a unix timestamp?
What are the advantages of using varchar type or int type? Any other type i should consider?

My problem is that i will be having a lot of scripts for comparing the date fields and I just wanna make sure I do my best from the beginning.
There will be a lot of entries in the db and i'm looking for the most efficient memory and processing usage as well as fastest time for queries.

Bottom line:
What is the best type? Why?

[omoutop]

so far, from personal experience, i prefer to save timestamps as intergers. Saves me the trouble to convert any other type to interger, to help myself in performing maths with timestmps.

Why save in any other type (varchar for example)?
Timestamt is a number - why save it as text?

[bilenkyj]

if your timestamp contains a date, dont you want to store it in your database as a date type so that any queries you run on the data can be done via the database and not php script?

[bilenkyj]

sorry wrong end of stick

[cnapsys]

thanks for the replies guys... omoutop, it makes perfect sense what you're saying and I was leaning towards int from the beginning, is just that I've seen a lot of people storing it into a varchar type field. varchar is alphanumeric which means that it can contain letters, numbers, spaces, symbols, and punctuation.
My only doubt was... why use varchar over int? faster execution time? or what?
Indeed it makes more sense of using int, considering you want to compare numbers to numbers yet varchar can hold numbers just fine...
I guess there is no big difference, just wanted to try and understand why would one choose varchar over int...

[omoutop]

if you do save it as varchar, remember to change the type into interger, before doing any maths with it.
gettype(), is_int() will help you get starting.

Another caution: you must check your string length and see if its a valid one, restrict your db table to accept only specific length strings, and more precautions .... only because you do no use int as your field type.

On te other hand you can save your dates as date in your mysql db - you can perform various things directly into the db instead of the script, like bilenkyj suggested.
One thing... you need to convert your db dates with mktime() or strtotime(), before posting them to your pages.
It is more convinient to see March 28, 2008 (or even 28-03-2008), insted of 2008-03-27 13:45:12 (db format)

[penders]

On te other hand you can save your dates as date in your mysql db - you can perform various things directly into the db instead of the script, like bilenkyj suggested.

Yes, just to reiterate... I would have thought storing your dates in your DB's own date format rather than a unix timestamp was perhaps the better solution, unless you have a valid reason not to? Particularly since you state:

i'm looking for the most efficient memory and processing usage as well as fastest time for queries.

time for query = time to process DB query + time for PHP to process the results.