MySQL - Search Timestamp by Hour of Day

mysql - search timestamp by hour of day

You could use the HOUR() function:

SELECT * FROM 'table' WHERE HOUR(`updatetime`) = 9

Alas, this query's performance will be horrible, as soon as you go over a few thousand rows - functions aren't indexable, so there will be a full table scan each time this query runs.

What we did in a similar situation: we created another column updatetime_hour, indexed it, and populated it on insert (and updated on update); then the query becomes fast:

SELECT * FROM 'table' WHERE `updatetime_hour` = 9

Yes, we have denormalized the data, and it's a bit more housekeeping, but I have yet to see a faster solution. (We considered and measured insert and update triggers to populate the updatetime_hour from updatetime, but decided against for performance; see if they would be useful for you.)

Select where unix timestamp falls on specified hour or day

It seems to me you want to choose rows where your date_updated value lies in a range of time. For example, I think you mean, if NOW() is 2017-04-03 17:55:22, you want all the records timestamped between 2017-04-03 14:00:00 and 2017-04-03 14:59:59.99999 inclusive.

Here's how you do that in a sargable way: a way that can use an index on your date_updated column.

This expression truncates NOW() to the top of the hour: 17:55 to 17:00:

 DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')

This backs up three hours.

 DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 3 HOUR

The beginning of your timestamp range is:

 UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 3 HOUR)

The end of your timestamp range, then, is

 UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 2 HOUR)

So, this WHERE clause does the trick.

 WHERE l.date_updated >= UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00')-INTERVAL 3 HOUR)
   AND l.date_updated) < UNIX_TIMESTAMP(DATE_FORMAT(NOW(),'%Y-%m-%d %H:00:00')-INTERVAL 2 HOUR)

Notice the <, not <=, at the end of the time range.

There's some time zone stuff happening here. Unix timestamps are (or should be) always recorded with respect to UTC. The UNIX_TIMESTAMP() function always converts from local time to UTC, and the NOW() function always works in local time, so this all should work properly. But you might investigate all this timestamp junk if you're still getting the wrong rows, or no rows.

Please notice that your time precision would be the same and your life would be easier if your column had the TIMESTAMP data type rather than the INT data type.

SQL where time in timestamp

This should work:

SELECT * FROM table
WHERE
(HOUR(date) BETWEEN 18 AND 21) OR
(HOUR(date) = 17 AND MINUTE(date)>=30) OR
(HOUR(date) = 22 AND MINUTE(date)<=30);

Or another approach would be to convert to DATE, add the hours and minutes and then use between.

SELECT * FROM table
WHERE date BETWEEN
ADDDATE(ADDDATE(DATE(date), INTERVAL 17 HOUR), INTERVAL 30 MINUTE)
AND
ADDDATE(ADDDATE(DATE(date), INTERVAL 22 HOUR), INTERVAL 30 MINUTE);

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

MySQL searching timestamp columns by date only

The problem here is that Timestamp datatype will have HH:MM:SS (time) values also. While comparing a datetime with date, MySQL would automatically assume 00:00:00 as HH:MM:SS for the date value.

So, 2018-10-10 12:23:22 will not match the following condition: created_at <= '2018-10-10'; since it would be treated as: 2018-10-10 12:23:22 <= '2018-10-10 00:00:00, which is false

To handle this, you can add one day to the date (date_to in the filter), and use < operator for range checking.

SELECT
    count(*)
FROM
    contact
WHERE
    created_at >= '2018-10-10'
    AND created_at < ('2018-10-10' + INTERVAL 1 DAY);

Query to select current and previous hour, day and month

Here is an example of how you can use MySQL date functions to create dates:

SELECT 
    NOW() AS CurrentDateTime, 
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') AS CurrentHour, 
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 1 HOUR AS PreviousHour, 
    CURDATE() AS CurrentDate, 
    CURDATE() - INTERVAL 1 DAY AS PreviousDate, 
    DATE_FORMAT(NOW(), '%Y-%m-01') AS CurrentMonth, 
    DATE_FORMAT(NOW(), '%Y-%m-01') - INTERVAL 1 MONTH AS PreviousMonth

Output:

CurrentDateTime: 2013-01-08 21:00:51
    CurrentHour: 2013-01-08 21:00:00
   PreviousHour: 2013-01-08 20:00:00
    CurrentDate: 2013-01-08
   PreviousDate: 2013-01-07
   CurrentMonth: 2013-01-01
  PreviousMonth: 2012-12-01

And here is example usage:

-- SELECT PREVIOUS HOUR ROWS
SELECT *
FROM table1
WHERE ADDTIME(Datestamp, Timestamp) >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00') - INTERVAL 1 HOUR
AND   ADDTIME(Datestamp, Timestamp) <  DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')

-- SELECT CURRENT HOUR ROWS
SELECT *
FROM table1
WHERE ADDTIME(Datestamp, Timestamp) >= DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')

Likewise for other cases.

MySQL TIMESTAMP BETWEEN 2 hours ago and 3 days ago

It's because you're using DATE(NOW()) instead of just NOW(). This discards the time of day, so it subtracts 2 hours from the beginning of the day (i.e. it returs 10pm on the previous day), rather than 2 hours before now.

AND (`placed_ts` BETWEEN
    DATE_SUB(DATE(NOW()), INTERVAL 3 DAY)
    AND DATE_SUB(NOW(), INTERVAL 2 HOUR))     

Create a by Date and hour breakdown from TIMESTAMP field MYSQL

Ok this took some time. This is what I could best come up with. May be there is a better way to do it but the missing date or time while joining is always complicated. I have given few answers related to missing dates but this is completely different.

So using the same idea I did some changes within the query to retrieve the data as below

select
t1.date_received,
t1.Hours,
COALESCE(t2.`Leads`,0) as `Leads`
from
(
  select
  distinct DATE(received) as date_received,
  concat (DATE(received),'-',h.hour) as date_hour,
  CONCAT(h.hour, ':00 - ', h.hour+1, ':00') AS Hours 
  from digital_lead
  cross join (select hour from hourly)h
)t1
left join
(
  SELECT DATE(received) as date_received,
  concat (DATE(received),'-',hour) as date_hour,
  CONCAT(hourly.hour, ':00 - ', hourly.hour+1, ':00') AS Hours,  
  COALESCE(COUNT(id),0) AS "Leads"
  FROM hourly
  LEFT JOIN digital_lead ON hourly.hour=hour(digital_lead.received)
  and digital_lead.received>=DATE_SUB('2014-11-01', INTERVAL 24 HOUR)
  and digital_lead.received<=DATE_SUB('2014-11-26', INTERVAL 24 HOUR) 
  GROUP BY DATE(received), hourly.hour
)t2
on t1.date_hour = t2.date_hour
order by t1.date_received,
cast(substring_index(t1.Hours,':',1) as unsigned)

FIDDLE

---

Related Topics