mysql select date using timestamp

Question

in my database i use a column named startdate and in the column there are rows with timestamps, looking like: 1410178260

Normally, when i use a datetime field and i want to select all the items with the date of today, i run this query:

$sql = "SELECT id FROM agenda2 WHERE DATE(startdate) >= CURRENT_DATE()";

But now, using the timestamps, i don't know how to make a query that selects all the items inserted today. Can someone help me with that?

Abhik Chakraborty · Accepted Answer · 2014-09-09 07:45:37Z

1

You need to convert to date using the function from_unixtime()

mysql> select FROM_UNIXTIME('1410178260');
+-----------------------------+
| FROM_UNIXTIME('1410178260') |
+-----------------------------+
| 2014-09-08 17:41:00         |
+-----------------------------+

So you may do as

SELECT id FROM agenda2 WHERE DATE(FROM_UNIXTIME(startdate)) >= CURRENT_DATE()

answered Sep 9, 2014 at 7:45

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

2 Comments

M Khalid Junaid Over a year ago

A Sargable query will be SELECT id FROM agenda2 WHERE startdate >= UNIX_TIMESTAMP(CURRENT_DATE())

Abhik Chakraborty Over a year ago

Yes Sargable query will make it better in terms of performance when the index is involved :)

VMai · Accepted Answer · 2014-09-09 07:45:19Z

0

If you're using unix timestamps then you've got to use

$sql = "SELECT id FROM agenda2 WHERE DATE(FROM_UNIXTIME(startdate)) >= CURRENT_DATE()";

Consider that both versions can't make use of an index.

see FROM_UNIXTIME

answered Sep 9, 2014 at 7:45

VMai

10.4k9 gold badges28 silver badges36 bronze badges

Collectives™ on Stack Overflow

mysql select date using timestamp

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related