Where current timestamp between two unix timestamps SQL query

Question

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

Can you provide a sample of the date_start and date_end values as well as the schema definition? — hafichuk
– hafichuk, Commented Dec 14, 2011 at 20:58
Should work, unless your columns are not of a proper date type. — Michael Berkowski
– Michael Berkowski, Commented Dec 14, 2011 at 20:59

Michael Berkowski · Accepted Answer · 2011-12-14 21:04:28Z

3

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.

edited Dec 14, 2011 at 21:04

answered Dec 14, 2011 at 20:57

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Karem Over a year ago

Thank you this worked!! can you tell me why my current_timestamp comparing with the other timestamps, wouldnt work?

Michael Berkowski Over a year ago

@Karem CURRENT_TIMESTAMP is a synonym for NOW() so you could use that instead. But you would need to convert it to a UNIX timestamp from the native MySQL datetime format as UNIX_TIMESTAMP(CURRENT_TIMESTAMP). Above, I did the opposite, converting your two UNIX timestamps to MySQL format instead...

Wayne · Accepted Answer · 2011-12-14 21:19:04Z

2

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.

edited Dec 14, 2011 at 21:19

Wayne

60.5k15 gold badges135 silver badges129 bronze badges

answered Dec 14, 2011 at 20:59

WWW

9,8782 gold badges32 silver badges33 bronze badges

Comments

Logan Serman · Accepted Answer · 2011-12-14 20:58:00Z

0

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.

answered Dec 14, 2011 at 20:58

Logan Serman

30k28 gold badges107 silver badges144 bronze badges

2 Comments

hafichuk Over a year ago

would you be able to provide a reference for not being able to use CURRENT_TIMESTAMP twice in a query?

hafichuk Over a year ago

AFAIK, CURRENT_TIMESTAMP is the same as NOW()

Collectives™ on Stack Overflow

Where current timestamp between two unix timestamps SQL query

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related