Sorting on time() and date()

StevenJ

having decided to add extra functionality to a program, I am stuck on a couple of parts.

I use

$filename = time()
$timestamp = $filename ;

to get the 10 digit value for the timestamp & filename. I store this value in a MySQL DB. No Problem there, except the field type I am storing the $timestamp.

If I set the field for the timestamp, which I have called 'time', as TEXT(10) it inserts the record fine and the 10 digit timestamp is visible.
If I set the 'time' field to TIMESTAMP the record is inserted, but the value in the table is 00000000000000 (14 0's)

What type should I be using?

anyway, to allow me to continue - I have used the text(10) type for now.

When I wish to print the date that forms part of the record, I simply do

echo date ("format I want", $row_record['time']) ;

and this converts the 10 digits into a readable date.

Now, If I wanted to select the records from the last 7 days, how would be best going about this?

would it simply be best doing

$timenow = time ();
$7daysago = $timenow - ( 60 * 60 * 24 * 7 ) ;

and select records where the 'time' field is > than the $7daysago?

while I am sure I can get it to work this way, is there not a better way of doing this? given that the 'time' field is text, will this > than < than operation work correctly?

cbj4074

< and > operations may work as intended on TEXT columns, but ORDER BY clauses will not. You most likely want to use DATE. Check out the related MySQL manual page for descriptions of each type: http://dev.mysql.com/doc/refman/5.1/en/datetime.html.

There is a similar, recent thread http://phpbuilder.com/board/showthread.php?t=10317727 that details some of this MySQL date stuff. Read this first.

And, for reference, it's probably worth checking out the MySQL reference manual to bring yourself up to speed on MySQL's capabilities with respect to dates.

Let us know if you still need help...

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Nibinaear

As long as the field is a DATE type it should be fine. Try running your query on the database directly first (via command prompt or GUI) to make sure it enters the data.