CODEWITHSUNDEEP
mysql
Jordan M
Jordan M

Reputation: 63

MySQL: Select records after a certain time yesterday

I'm having a bit of a brain freeze here and can't get my head around this. I want a query that will dynamically give me the records after 7:30am yesterday. I want to just run the same query every day and have it pull all the records after 7:30am yesterday. The other trick is that the timezone I'm running in is 8 hours off the server time. I've got all the components (I think) it's just getting them together in the right order.

date_sub(addtime(now(),'8:00'), interval 1 day)

The above gets me exactly 24 hours ago but I need yesterday after 07:30. Any clues how I can do this? Thanks in advance.

Jordan

Upvotes: 1

Views: 2114

Answers (1)

afenster
afenster

Reputation: 3608

You can use curdate() instead of now() to get the 00:00:00 of the current date:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2014-08-26 06:21:03 |
+---------------------+
1 row in set (0.00 sec)

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2014-08-26 |
+------------+
1 row in set (0.00 sec)

Now we can select yesterday:

mysql> select date_sub(curdate(), interval 1 day);
+-------------------------------------+
| date_sub(curdate(), interval 1 day) |
+-------------------------------------+
| 2014-08-25                          |
+-------------------------------------+
1 row in set (0.00 sec)

Yesterday at 7:30:

mysql> select date_add(date_sub(curdate(), interval 1 day), interval '7:30' HOUR_MINUTE);
+----------------------------------------------------------------------------+
| date_add(date_sub(curdate(), interval 1 day), interval '7:30' HOUR_MINUTE) |
+----------------------------------------------------------------------------+
| 2014-08-25 07:30:00                                                        |
+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

To change the timezone for your session you can use

mysql> set time_zone = '+00:00';

Upvotes: 7

Related Questions