Posted by Kevin L'Huillier on 06/27/05 16:38

On 27/06/05, Jasper Bryant-Greene <jasper@bryant-greene.name> wrote:
> John Taylor-Johnston wrote:
> > I could just change the field type. But how do you calculate it? I don't
> > see much to inspire a start. I'm not a full-time coder either. More of a
> > tinkerer. I don't want someone to do it for me, but need to get my head
> > around how to do it.
> > http://ca3.php.net/manual/en/function.strtotime.php
>
> As I said, though, you should be using a MySQL date field. Have a look
> at the MySQL manual for the corresponding functions to the above --
> there's probably a quicker way with MySQL too.

Dates should almost always be stored as dates in a database, not
varchars. This allows for more and/or faster date functions, sorting
that works (it only works with yyyy-mm-dd format in varchars), and you
can select the data into a unix timestamp instead of needing to
strtotime() first.

The calculation in mysql couldn't be much simpler.

select datediff(StampDate, '2003-08-23') as StampDateDiff


From the manual:

DATEDIFF() returns the number of days between the start date expr and
the end date expr2. expr and expr2 are date or date-and-time
expressions. Only the date parts of the values are used in the
calculation.

mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1

http://mysql.org/doc/mysql/en/date-and-time-functions.html

[Back to original message]