Anu Anu - 7 months ago 17
SQL Question

How to select records form a table with current date and custom time range?

I am having a table with seperate

date and time
column. I need to select all data from my table checking two condition

1.records with current date(todays date).

2.records with custom time range.

This is my table structure

+------------+------------+---------------------+-------------------+--------------------+
| id | item | description | bill_date | bill_time |
+------------+------------+---------------------+-------------------+--------------------+
| 1 | x | test | 2016-04-15 | 12:05:00 |
+------------+------------+---------------------+-------------------+--------------------+
| 2 | y | test1 | 2016-04-15 | 01:10:44 |
+------------+------------+---------------------+-------------------+--------------------+
| 3 | z | test2 | 2016-04-16 | 05:10:10 |
+------------+------------+---------------------+-------------------+--------------------+


I could select time range using this mysql query

SELECT * FROM `bill_item` WHERE `bill_time` BETWEEN '12:00:00' AND '06:00:00'


this returns
2,3 record
now I need to check if the record is todays record. How to do this?

Answer

It is possible to have multiple conditions in the WHERE clause. You can use CURRENT_DATE() or NOW() to get only todays entries.

SELECT * 
FROM `bill_item` 
WHERE (bill_time BETWEEN '12:00:00' AND '06:00:00') 
  AND bill_date = CURRENT_DATE()

See the official documentation for more Date and Time Functions

Comments