Date storage format

Good day, and Happy New Year!

I'm trying to teach myself some PHP and MySQL and the project that I've
decided to build will be dependent on queries involving comparisons of
dates. I'd like some advice, please, on whether I should -

1. Store the date (2008-01-01) in the MySQL table as a date and compare to
that, or...
2. Create a/ timestamp/s for the date/s in question, store the timestamp and
then compare to that.

If the timestamp is the way to go, should that column be of type "Timestamp"
or will that cause the string to be altered to a MySQL timestamp?

Thank you in advance for any assistance.


Steve E.

Re: Date storage format

On Tue, 01 Jan 2008 15:22:19 +0100, Serious_Practitioner
wrote:
> Good day, and Happy New Year!

The same to you.

> I'm trying to teach myself some PHP and MySQL and the project that I've
> decided to build will be dependent on queries involving comparisons of
> dates. I'd like some advice, please, on whether I should -
>
> 1. Store the date (2008-01-01) in the MySQL table as a date and compare
> to
> that, or...
> 2. Create a/ timestamp/s for the date/s in question, store the timestamp
> and
> then compare to that.
>
> If the timestamp is the way to go, should that column be of type
> "Timestamp"
> or will that cause the string to be altered to a MySQL timestamp?

If you plan to base a lot of queries on date lookups/comparisons, go for
the native date format in MySQL. Any formatting of a date-output can be
done with MySQL itself. Should you need a timestamp for some reason in the
PHP code itself (be very sure you need this... MySQL might be more capable
then you think), you can always query the timestamp using UNIX_TIMESTAMP()
on the column.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functio ns.html
--
Rik Wasmus

Re: Date storage format

Serious_Practitioner wrote:
> Good day, and Happy New Year!
>
> I'm trying to teach myself some PHP and MySQL and the project that I've
> decided to build will be dependent on queries involving comparisons of
> dates. I'd like some advice, please, on whether I should -
>
> 1. Store the date (2008-01-01) in the MySQL table as a date and compare to
> that, or...
> 2. Create a/ timestamp/s for the date/s in question, store the timestamp and
> then compare to that.
>
> If the timestamp is the way to go, should that column be of type "Timestamp"
> or will that cause the string to be altered to a MySQL timestamp?
>
> Thank you in advance for any assistance.
>
>
> Steve E.
>
>
>
>
>

Try comp.databases.mysql - where the MySQL experts hang out. They can
tell you the best way to do it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Date storage format - Thanks!

Thank you, Rik and Jerry. I'll follow the lkinks and suggestions that you
made.


Steve E.




"Jerry Stuckle" wrote in message
news:BvudnYhhb9bOzefanZ2dnUVZ_qjinZ2d@comcast.com...
> Serious_Practitioner wrote:
>> Good day, and Happy New Year!
>>
>> I'm trying to teach myself some PHP and MySQL and the project that I've
>> decided to build will be dependent on queries involving comparisons of
>> dates. I'd like some advice, please, on whether I should -
>>
>> 1. Store the date (2008-01-01) in the MySQL table as a date and compare
>> to that, or...
>> 2. Create a/ timestamp/s for the date/s in question, store the timestamp
>> and then compare to that.
>>
>> If the timestamp is the way to go, should that column be of type
>> "Timestamp" or will that cause the string to be altered to a MySQL
>> timestamp?
>>
>> Thank you in advance for any assistance.
>>
>>
>> Steve E.
>>
>>
>
> Try comp.databases.mysql - where the MySQL experts hang out. They can
> tell you the best way to do it.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================
>