Query on a Time Range Ignoring The Date of Timestamps

Query on a time range ignoring the date of timestamps

You need to extract just the hour portion from created_at using PostgreSQL's date_part/extract function.

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 20

For example, something like this:

Purchases.where(["EXTRACT(HOUR FROM created_at) BETWEEN ? AND ?", 13, 14])

Select Timestamp By Time Ignoring Date

SELECT * FROM Table WHERE TIME(Time) = '10:09:13'

That should do it.

SQlite: Select rows within a specific time range, ignoring the date

If I read your question correctly, you want to use your WHERE clause to restrict to any calendar date in 2016 between 12 and 13 hours. In this case, you can use STRFTIME to extract the year and hour in string format from your datetime column.

SELECT COUNT(message)
FROM messages
WHERE STRFTIME('%Y', datetime) = '2016' AND
      STRFTIME('%H', datetime) < '13' AND
      STRFTIME('%H', datetime) > '12'

Note that the reason while the inequalities should work with strings is because numerical strings still sort based on their lexigraphical order.

Update:

Since your datetime column is in a non standard format, you may be able to workaround this by substringing off the various pieces you need to use in the WHERE clause:

SELECT COUNT(message)
FROM messages
WHERE SUBSTR(datetime, 1, 4) = '2016' AND
      SUBSTR(datetime, 12, 2) < '13' AND
      SUBSTR(datetime, 12, 2) > '12'

How to select rows for a specific date, ignoring time in SQL Server

You can remove the time component when comparing:

SELECT * 
FROM sales 
WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, salesDate))) = '11/11/2010'

Another approach is to change the select to cover all the time between the start and end of the date:

SELECT * 
FROM sales 
-- WHERE salesDate BETWEEN '11/11/2010 00:00:00.00' AND '11/11/2010 23:59:59.999'
WHERE salesDate BETWEEN '2020-05-18T00:00:00.00' AND '2020-05-18T23:59:59.999'

Getting today's record in MySQL ignoring time and hour

If you want all the records for the current date where the values are stored as 2015-12-08 15:30:12 you can easily use date() function over the column in the select something as

where date(rdate_collect) = curdate()

The disadvantage is it will never use index even if the column is indexed and when you have large amount of data it would create an issue since the query will be slow.

So you can format the query as

where 
rdate_collect >= concat(curdate(),' ','00:00:00')
and rdate_collect <= concat(curdate(),' ','23:59:59')

Here is a live example, I have a login_audit table and it has a column last_login(datetime) and its indexed.

So lets see step by step

mysql> select count(*) from login_audit ;
+----------+
| count(*) |
+----------+
|  5188680 |
+----------+
1 row in set (0.00 sec)

I have large number of records into the table

Now lets use date() function and see what it shows in explain plan

mysql> explain select count(*) from login_audit where date(last_login) = curdate() ;
+----+-------------+-------------+-------+---------------+----------------+---------+------+---------+--------------------------+
| id | select_type | table       | type  | possible_keys | key            | key_len | ref  | rows    | Extra                    |
+----+-------------+-------------+-------+---------------+----------------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | login_audit | index | NULL          | last_login_idx | 5       | NULL | 5188680 | Using where; Using index |
+----+-------------+-------------+-------+---------------+----------------+---------+------+---------+--------------------------+

From the explain plan looks like mysql may scan the entire table and it will be a slow query.

Now change the query and see

mysql> explain select count(*) from login_audit where last_login >= concat(curdate(),' ','00:00:00') and last_login <= concat(curdate(),' ','23:59:59') ;
+----+-------------+-------------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table       | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | login_audit | range | last_login_idx | last_login_idx | 5       | NULL |    1 | Using where; Using index |
+----+-------------+-------------+-------+----------------+----------------+---------+------+------+--------------------------+

So yes this time its better

And here is the time difference in the query

mysql> select count(*) from login_audit where date(last_login) = curdate() ;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.92 sec)

mysql> select count(*) from login_audit where last_login >= concat(curdate(),' ','00:00:00') and last_login <= concat(curdate(),' ','23:59:59') ;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

TSQL: Date BETWEEN Query - Ignoring Time

Just use DATEADD for the enddate to set it to midnight on the NEXT day...

TheDate BETWEEN @EnteredBeginDate AND DATEADD(day, 1, @EnteredEndDate)

If you want to be really precise, you could subtract a second or millisecond from that to make it 11:59:59 on your specified date:

TheDate BETWEEN @EnteredBeginDate AND DATEADD(second, -1, (DATEADD(day, 1, @EnteredEndDate)))

Query timestamps in Google Sheets looking for specific date but ignoring the time

See if this helps

=query(ImportedData1!A1:AF, "Select avg(AF) where toDate(A) = date '2019-04-08' label avg(AF) '' ", 0)

How to only check the time on datetime fields but ignore the date?

You only need a minor tweak on what you already have.

SELECT  *
FROM    progen.DY
WHERE   TIME(DY_DATE) <> '00:00:00:000'

Use CONVERT to change your DATETIME to a TIME.

SELECT  *
FROM    progen.DY
WHERE   CONVERT(TIME, DY_DATE) <> '00:00:00:000'

Comparing dates in MySQL ignoring time portion of a DateTime field

You could use the DATE function:

SELECT col1, col2, ..., coln
FROM order_table
WHERE date(order_date) = '2012-05-03'

But this is more efficient, if your table is large and you have an index on order date:

SELECT col1, col2, ..., coln
FROM order_table
WHERE order_date >= '2012-05-03'
AND order_date < '2012-05-04'

Select a date range from a timestamp column

If you are going to cast the timestamp values of the field to date, as suggested in another answer, performance will degrade because every single value in the column needs to be cast for comparison and simple indexes cannot be used. You would have to create a special index on the expression, like so:

CREATE INDEX some_idx ON tbl (cast(mdate AS date));

In this case the query should also be simplified to:

SELECT * FROM tbl
WHERE  mdate::date = '2011-09-12'::date; -- 2nd cast optional

However, as far as I can see, your problem is a simple typo / thinko in your query:

You switched upper & lower boundaries. Try:

SELECT * FROM tbl
WHERE  mdate >= '2011-09-12 00:00:00.0'
AND    mdate <= '2011-09-13 00:00:00.0';

Or, to simplify your syntax and be more precise:

SELECT * FROM tbl
WHERE  mdate >= '2011-09-12 00:00'
AND    mdate <  '2011-09-13 00:00';

• There is no need to spell out seconds and fractions that are 0.
• You don't want to include 2011-09-13 00:00, so use < instead of <=.
  
  Don't use BETWEEN here, for the same reason:

WHERE mdate BETWEEN '2011-09-12 00:00' AND '2011-09-13 00:00'

This would include 00:00 of the next day.

Also be aware that a column of type timestamp [without time zone] is interpreted according to your current time zone setting. The date part depends on that setting, which should generally work as expected. More details:

• Ignoring time zones altogether in Rails and PostgreSQL

---

Related Topics