CODEWITHSUNDEEP
mysqlsql
Karem
Karem

Reputation: 18103

Where current timestamp between two unix timestamps SQL query

Im trying to make a query where time() is between date_start and date_end i tried:

date_start > CURRENT_TIMESTAMP AND date_end < CURRENT_TIMESTAMP

and

WHERE CURRENT_TIMESTAMP BETWEEN date_start AND date_end

Still it does not workout for me. Tried CURRENT_TIMESTAMP() too.

Mysql

id  item_id percentage  rabatcode   date_start  date_end
9   6       50          XXY-GGS-82  1323817200  1324422000

Upvotes: 0

Views: 13799

Answers (3)

WWW
WWW

Reputation: 9860

You mention in the title of your question that you want to check when the current time is between to UNIX timestamps, so you need to use the UNIX_TIMESTAMP() function:

WHERE UNIX_TIMESTAMP() BETWEEN date_start AND date_end

NOW() returns a string (from the manual: '2007-12-15 23:50:26') so it won't work for your purposes.

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270609

Compare NOW() against the values via FROM_UNIXTIME():

WHERE NOW() BETWEEN FROM_UNIXTIME(date_start) AND FROM_UNIXTIME(date_end)

Ideally, if it is possible for you to modify your database structure, it is usually much better to store dates as MySQL's native DATETIME type, owing to the fact that MySQL's date processing functions work with the date types without conversion.

Upvotes: 3

Logan Serman
Logan Serman

Reputation: 29870

The problem is you cannot use CURRENT_TIMESTAMP twice. You should look into converting your dates to a different format and comparing them to NOW() (if they cannot be compared to NOW() already.

Upvotes: 0

Related Questions