SQL Timestamp Problem

[jhl84]

This is the table I have:

No Name Timestamp Type
1 Jack Mon Nov 19 12:19:49 UTC+0800 2007 A
2 Dave Tue Nov 20 8:38:13 UTC+0800 2007 A
3 Jack Mon Nov 12 12:19:49 UTC+0800 2007 A
4 Jack Sun Nov 18 12:19:49 UTC+0800 2007 A
5 Jack Sun Nov 18 12:19:49 UTC+0800 2007 B

Can I select the same Name with the same Type where the timestamp is less than 7 days from today's date? In this case, use 21st Nov as this is today's date. From the example I will get 1 - Jack and 4 - Jack.

The format of the time in the table is really troublesome for me. Please advice. Thanks.

[CFMaBiSmAd]

Since you posted this in the mysql forum section, I will assume that you are using mysql. That time is not a standard mysql DATETIME data type and is probably just a string field type. That makes it extremely difficult to use. If this was a standard mysql DATETIME data type there are about 30+ built in mysql date and time functions you could use and things like comparisons and ORDER BY would work directly.

So, the recommendation to make your life easier, would be to convert this into a standard mysql DATETIME data type. The easiest way would be to add a DATETIME column and then set it to the corresponding value. Convert and test your code to use the new DATETIME column and then once you are satisfied with the results, delete the old column.

You can use the mysql str_to_date() function in a query to populate the new DATETIME column based on what is in the old column - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

Also, use the mysql date_format() function to output a standard DATETIME field in the format you want in a SELECT query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

[jhl84]

it wasnt me who design the db. i following up on someone else's work and the time is giving me headache. yea indeed is a string field type. just wondering if there's a way to extract it as time format n use time function to calculate days.

anyhow thanks mate.

[Fumigator]

There is-- but you need to do what CFMaBiSmAd suggested and make the column a datetime type column first.