Comparing timestamp with date variable (MySQL and PHP)

Question

Any help is highly appreciated.

Say I have a MySQL database with a timestamp column with the value "1305590400", how can I compare that with a PHP variable of say "2011-05-17"? I want to completely ignore the time part and only compare the date.

At the moment I am trying to get it working with the following but it returns no results:

WHERE  FROM_UNIXTIME('timestamp_column','%Y-%m-%d') = '" . $date. "'

zerkms · Accepted Answer · 2011-05-17 00:35:38Z

5

You don't get results that probably $date has some time offset and is not equal to 00:00:00 in your timezone.

WHERE timestamp_column BETWEEN '" . strtotime($date) . "' AND '" . strtotime($date, '+1 day') . "'

or to be more precise:

    WHERE timestamp_column >= '" . strtotime($date) . "'
      AND timestamp_column < '" . strtotime($date, '+1 day') . "'

edited May 17, 2011 at 0:35

answered May 17, 2011 at 0:26

zerkms

256k73 gold badges447 silver badges551 bronze badges

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

3 Comments

David Over a year ago

The timestamp_column contains random times, is there a way to ignore the time and just compare the date?

zerkms Over a year ago

@David: that is why I added BETWEEN. Currently query retrieves everything between today noon and tomorrow noon

David Over a year ago

The only problem I have is that I'm using this in a calendar script and I don't think it will return the right results, I will definitely try it out now though.

Thomas Hupkens · Accepted Answer · 2011-05-17 00:26:45Z

0

Simply

SELECT [...] WHERE '$you_var' = date(`timestamp_column`);

MySQL date() function

answered May 17, 2011 at 0:26

Thomas Hupkens

1,60010 silver badges18 bronze badges

3 Comments

zerkms Over a year ago

How mysql uses indexes

Thomas Hupkens Over a year ago

I assume that this link will convince me that my solution is slower, am I right? ^^

zerkms Over a year ago

yep. Your query will always cause fullscan. If you have a lot of rows and need to find fast - don't apply functions for the fields in your WHERE.

user2055770 · Accepted Answer · 2013-02-08 20:41:45Z

0

You could always convert the timestamp to a formatted date.

SELECT FROM_UNIXTIME(1358871753,'%Y %D %M');

Produces: 2013 22nd January

answered Feb 8, 2013 at 20:41

user2055770

1

Collectives™ on Stack Overflow

Comparing timestamp with date variable (MySQL and PHP)

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related