Selecting Entries by Date - >= Now(), MySQL

Selecting entries by date - = NOW(), MySQL

You are looking for CURDATE():

$db->query("SELECT * FROM events WHERE event_date >= CURDATE()");

Or:

$db->query("SELECT * FROM events WHERE event_date >= CURRENT_DATE()");

MySQL Select Date Equal to Today (having datetime as the data type)

SELECT users.id, DATE_FORMAT(users.signup_date, '%Y-%m-%d') 
FROM users 
WHERE DATE(signup_date) = CURDATE()

How to select rows that have current day's timestamp?

use DATE and CURDATE()

SELECT * FROM `table` WHERE DATE(`timestamp`) = CURDATE()

Warning! This query doesn't use an index efficiently. For the more efficient solution see the answer below

see the execution plan on the DEMO

Select records from NOW() -1 Day

Judging by the documentation for date/time functions, you should be able to do something like:

SELECT * FROM FOO
WHERE MY_DATE_FIELD >= NOW() - INTERVAL 1 DAY

Select data between a date/time range

You need to update the date format:

select * from hockey_stats 
where game_date between '2012-03-11 00:00:00' and '2012-05-11 23:59:00' 
order by game_date desc;

MySQL to return records with a date/time of now minus 1 hour?

You are very close. The most readable way, in my opinion, to write WHERE conditions involving date / times is:

 WHERE date_of_event >= NOW() - INTERVAL 1 HOUR

Why? >= for date/time values means on or after. You need to be able to look at your query code and reason about it. That formulation clearly matches your specification:

a date/time equal to or more than now - 1hour?

But: you say you want to trigger an event one hour after the time. That probably means you want to choose

a date/time one hour or more ago, that is a date/time equal to or less than now - 1 hour.

That's what you have already. But I would rewrite it

WHERE date_of_event <= NOW() - INTERVAL 1 HOUR

<= means on or before.

Pro tip Avoid backticks unless your columns or tables have the same names as reserved words like SELECT or GROUP. Avoid naming your columns or tables with reserved words. Backticks look so much like single-quotes that it's easy to get confused.

Pro tip date/time expressions with = in them almost never come up true, because the equality must be exact, down to the second or millisecond. So avoid conditions like date_of_event = NOW() - INTERVAL 1 HOUR.

MySQL select datetime field where date equals today

You have to strip the time part of booked_at because 2015-08-05 09:10:56 is not equal to 2015-08-05. Try this:

select date(booked_at) from booking_dates where date(booked_at) = CURDATE();

MySQL query select all were date is equal to today on datetime

Can do this entirely in sql transaction ( no need for php date formatting ) :

SELECT * FROM table WHERE date(`datetime`) = current_date;

Datetime equal or greater than today in MySQL

SELECT * FROM users WHERE created >= CURDATE();

But I think you mean created < today

You can compare datetime with date, for example: SELECT NOW() < CURDATE() gives 0, SELECT NOW() = CURDATE() gives 1.

MySQL query to select events between start/end date

If I understood correctly you are trying to use a single query, i think you can just merge your date search toghter in WHERE clauses

SELECT id 
FROM events 
WHERE start BETWEEN '2013-06-13' AND '2013-07-22' 
AND end BETWEEN '2013-06-13' AND '2013-07-22'

or even more simply you can just use both column to set search time filter

SELECT id 
FROM events 
WHERE start >= '2013-07-22' AND end <= '2013-06-13'

---

Related Topics