CODEWITHSUNDEEP
mysql
robobobobo
robobobobo

Reputation: 759

Issue Using TimeStampDiff() In SQL Query

I've been trying to get my sql query to return the records which have been created in the last 7 days.

There is a timestamp record associated with each entry so I've used the TIMESTAMPDIFF function to write the following query

SELECT * FROM `messages` WHERE TIMESTAMPDIFF(DAY, time, NOW()) <= 7

For some reason this is returning a list of records from 2011!

Is this is a simple syntax error I'm doing wrong?

I have also tried using DATEDIFF()

SELECT * FROM `messages` WHERE DATEDIFF(NOW(), time) <= 7

But that seems to give me the same wrong results. I suspect this is because DATEDIFF isn't working with the UNIX timestamp?

I have another query that I use on another table which uses the DATEDIFF() query and it works fine, but in that table there is an actual date column, as opposed to a timestamp.

Sorry if this is a simple answer, but I've been playing around with this for a while now to no avail!

Upvotes: 0

Views: 1659

Answers (1)

Marty McVry
Marty McVry

Reputation: 2856

Try following query:

SELECT *
FROM messages
WHERE time > UNIX_TIMESTAMP(NOW()) - 604800

A little explanation: 7 days = 168 hours = 10 080 minutes = 604 800 seconds.

Since UNIX-timestamps are the number of seconds passed since 1970-01-01 00:00:00, you'd have to calculate using seconds. Most of the date/time functions in MySQL are useless then, since they only work with the native MySQL format for storing dates and times.

Upvotes: 1

Related Questions