CODEWITHSUNDEEP
mysql
robobobobo
robobobobo

Reputation: 759

Getting Records Between 2 Timestamps MySql

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!

Upvotes: 0

Views: 2886

Answers (1)

RET
RET

Reputation: 9188

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()) ";

Upvotes: 1

Related Questions