CODEWITHSUNDEEP
mysql
Roger Dodger
Roger Dodger

Reputation: 987

In MySql how do I get all rows that have a date of less than or equal to today at 5 am UTC time?

I have a MySql DB that consists of a table that holds all my data. I need to retrieve only those rows that have a startup_date that is less than or equal to today at 5 am UTC time. I tried looking up but it's kind of confusing. Can someone provide me a good, clean way of doing this?

My table is as follows:

  tbl_MyData
  ***********
  id      name       city        startup_date
  1      test1      New York    2020-01-10 18:19:30
  2      test2      Houston     2019-01-30 05:00:00
  3      test3      Chicago     2020-02-09 05:00:00

From the above data, my query should return ONLY row id number 2, since that is the only row that satisfies my criteria of startup_date being less than or equal to today at 5 am. Also, I'm not sure if I need to have any kind of UTC functions, since I'm not sure how mysql stores it's date time data.

Upvotes: 0

Views: 37

Answers (2)

G Schunk
G Schunk

Reputation: 23

What SELECT statement have you tried also what are the data types for the table? If startup-date is a date type in MySQL then it should just be 

SELECT * FROM tbl_MyData WHERE startup_date<=(whatever the value needs to be)

Upvotes: 1

GMB
GMB

Reputation: 222472

Use a simple WHERE clause 

SELECT t.* FROM mytable WHERE t.startup_date <= DATE_ADD(CURDATE(), INTERVAL 5 HOUR)

The CURDATE() function returns the current date without time (hence today at midnight), and the DATE_ADD() function adds 5 hours to it.

Upvotes: 1

Related Questions