CODEWITHSUNDEEP
mysqlsql
user256717
user256717

Reputation:

truncate now() to only hour in mysql

How do I truncate now() to get only upto hour precision in mysql?

In

select id from Run where submitTs <= now()  and submitTs >= (now()  - interval 1 hour);

I really want the records from 12.00 PM to 1 PM instead of 12.23 PM to 1.23 PM if I am running at 1.23 PM.

So how do I say 

  submitTs<= truncate_to_hour(now())

Upvotes: 1

Views: 6088

Answers (3)

Devart
Devart

Reputation: 122032

Try thsi query -

SELECT
  id
FROM
  Run
WHERE
  DATE(submitTs) = CURDATE() AND
  EXTRACT(HOUR FROM NOW()) = EXTRACT(HOUR FROM submitTs)

This query has better performance than first one - 

SELECT
  id
FROM
  Run
WHERE
  submitTs >= CURDATE() + INTERVAL EXTRACT(HOUR FROM NOW()) HOUR AND
  submitTs < CURDATE() + INTERVAL EXTRACT(HOUR FROM NOW()) + 1 HOUR

Upvotes: 3

Omesh
Omesh

Reputation: 29121

something like this should work:

SELECT id 
FROM Run 
WHERE submitTs BETWEEN CAST(CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d %h'), ':00:00') AS DATETIME)
      AND CAST(CONCAT(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 HOUR), '%Y-%m-%d %h'), ':00:00') AS DATETIME);

Upvotes: 1

stevenqb1890
stevenqb1890

Reputation: 44

I think some thing like this can help you to get hour of now():

HOUR(TIME(NOW())

Please see the reference at http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html to get more date-time function in mysql.

Upvotes: -1

Related Questions