Time thing

[Anon]

Hello,

Right here one for you. I've just been give a mysql DB to generate some stats from it. It has a varchar with a date line in it I think it's a standard format. eg.

Mon Jul 9 09:28:28 2001
Mon Jul 9 09:37:57 2001

and i want to include a bit that say x entries in the last 24 hours
and x entries in the last 7 days

I don't know where to start
any ideas?

[djg_uk]

Hi Ashley,

Dates should always be stored in a timestamp field - it makes things SO much easier! It will be very hard to compare values in their current format, especially as the field starts with a letter. The ideal layout would be YEARMONTHDAYHOURMINUTESECOND, as it can be easily sorted. I'd really recommend creating a script using mktime() to repopulate the database with timestamps instead.

When you have done this, you can easily grab a timestamp using time() and search for values greater than (time() - 86400) - 24 hours or (time() - 604800) one week.

I really cannot stress how bad the current design of the database is in terms of date manipulation..

Regards,

David

[Anon]

yeah i know it's bad i didn't make it someone else did they should be shot