Use Unix timestamp as a condition in mysql

Question

I have a table that is similar to the one below. As you can see, I have stored the dates as Unix timestamps. My goal is to only return the results which are for the upcoming dates, not the ones which are in past. How can I achieve this?

id    |   date
1       1331506800 //Mar 12 2012 
2       1366149600 //Apr 17 2013
3       1413928800 //Oct 22 2014
4       1436652000 //Jul 12 2015

Desire result:

id    |   date
1       1413928800 //Oct 22 2014
2       1436652000 //Jul 12 2015

zerkms · Accepted Answer · 2013-10-11 06:18:46Z

3

SELECT *
  FROM table
 WHERE date > UNIX_TIMESTAMP()

answered Oct 11, 2013 at 6:18

zerkms

256k73 gold badges447 silver badges551 bronze badges

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

4 Comments

Prof. Falken Over a year ago

This is the best MySQL answer.

user1973799 Over a year ago

Thanks bro! I knew you had this one!

Marcel Over a year ago

Watch the mysql keywords! Date is also a SQL function. So you have to place the name of the column in quotes like this: SELECT * FROM table WHERE `date` > UNIX_TIMESTAMP()

zerkms Over a year ago

@Marcel: watch the documentation dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Prof. Falken · Accepted Answer · 2013-10-11 06:18:12Z

1

$h->query("SELECT * FROM thetable WHERE date > " . time());

This will also work in other databases than MySQL which do not have UNIX_TIMESTAMP() function.

answered Oct 11, 2013 at 6:18

Prof. Falken

25.1k20 gold badges105 silver badges179 bronze badges

Comments

Sashi Kant · Accepted Answer · 2013-10-11 06:17:46Z

0

Try this::

Select * from table
where DATEDIFF(STR_TO_DATE(dateColumn, '%e %b %Y %k:%i')),now())>0

answered Oct 11, 2013 at 6:17

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

Collectives™ on Stack Overflow

Use Unix timestamp as a condition in mysql

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related