0

Quick one here, I'm trying to select all the records between two timestamps. The current time and the date Accepted. The Date Accepted is a UNIX timestamp. I am trying to select the records from a table called "messages" and the UNIX timestamp for each record is in the "time" column.

This is one of the queries I've tried

    $query = " SELECT * FROM `messages` WHERE `user_id` = $id \n"
. "And `time` between \"(UNIX_TIMESTAMP($timestamp)\" And UNIX_TIMESTAMP(NOW()) ";

It doesn't seem to be returning anything for me. I've tried so many different variations. I have also tried this - $timestamp is in Unix format..

$query = " SELECT * FROM `messages` WHERE `user_id` = $id \n"
. "And `time` between $timestamp And UNIX_TIMESTAMP(now()) ";

I'm sure it's something glaringly obvious, would much appreciate some input!

robobobobo
  • 739
  • 1
  • 9
  • 19

1 Answers1

1

I would debug this by independently running:

SELECT `user_id`, `time`,
       UNIX_TIMESTAMP($timestamp) AS ts_from, UNIX_TIMESTAMP(NOW()) AS ts_to
  FROM `messages`

... to confirm that the datatypes and values are what you anticipate. You might have to embed an example of $timestamp as a literal value to test the SQL in isolation.

There's also a possibility that the calculation of $timestamp uses a different time-zone setting to the database server. Depending on how recent these message records are, it could be a factor.

UPDATE

The issue is mixed casting here. UNIX_TIMESTAMP(x) takes a DATETIME and converts it to a UNIX timestamp. There's also an inverse function FROM_UNIXTIME(x) that takes a UNIX timestamp and converts it to a DATETIME.

In the predicate clause time BETWEEN x AND y, all 3 arguments need to be the same type.

So assuming your time column contains a DATETIME, I think you simply want:

$query = "SELECT * FROM `messages` WHERE `user_id` = $id "
       . "And `time` between FROM_UNIXTIME($timestamp) And NOW() ";

On the other hand, if time is a UNIX timestamp:

$query = "SELECT * FROM `messages` WHERE `user_id` = $id "
       . "And `time` between $timestamp And UNIX_TIMESTAMP(NOW()) ";
Community
  • 1
  • 1
RET
  • 9,100
  • 1
  • 28
  • 33
  • Thanks for your input RET, I tried testing that SQL statement but I was getting an error back and no value. I'm doing this in Joomla if that's any help. In your query, I can't seem to find out what `AS ts_to` means. I'm pretty certain $timestamp is correctly formatted as when I echo it it looks like this "1377648000". I've been able to get this working fine with dates, I just can't seem to understand the different syntax for timestamps! – robobobobo Aug 30 '13 at 19:25
  • See my updated answer. I can see what's going on now you've provided an example of what `$timestamp` contains. – RET Aug 31 '13 at 02:21
  • Ah yes brilliant that was it! Thanks a bunch, it was the second option I needed, converting the NOW() time to a UNIX Timestamp – robobobobo Sep 01 '13 at 17:00
  • 1
    Glad to help. MySQL has no consistency in function names - if they were called `FROM_UNIXTIME()` and `To_UNIXTIME()` (or even better `DATETIME_TO_TIMESTAMP()` and `TIMESTAMP_TO_DATETIME()`) it would make everyone's life a *lot* easier. – RET Sep 01 '13 at 20:50