How to get rows of last minute of every month?
You could use LAST_DAY
to get the last day of the month and DATE_FORMAT
to get the time to compare.
SELECT * FROM <table_name>
WHERE DATE_FORMAT(LAST_DAY(<date_time_col>),"%d")=DATE_FORMAT(<date_time_col>,"%d")
AND DATE_FORMAT(<date_time_col>,"%H:%i")='23:59';
Detailed Explanation :
So, basically, to get the correct row we need to get the last day of the month AND last minute of the day
LAST_DAY
will help use to get the last day of the month for the given date-time. And DATE_FORMAT
will help to get the date. Now, we will combine them together, to get the last date of the given date-time.
DATE_FORMAT(LAST_DAY(<date_time_col>),"%d")
Above will return 29
, if last day of the month is 29-02-2018
.
Now, we need to check, if given date-time has last minute of the day ? We can again make use of DATE_FORMAT
to extract time from the given date-time. Here, we will only concentrate on hour and minute (as per OP question). So, it should be
DATE_FORMAT(<date_time_col>,"%H:%i")
Above will return 23:59
, if given date-time is 29-02-2018 23:59:00
.
SQL select records only from last 5 minutes
This is your where clause:
where timestamp between curtime() and timestamp(date_sub(now(), interval 5 minute))
In this case, the first bound is larger than the second, because you are subtracting 5 minutes from the current time. This actually should not match anything, because:
where x between y and y - 5
should always return an empty set. The bounds are in order. Perhaps swapping them will help:
where timestamp between timestamp(date_sub(now(), interval 5 minute)) and curtime()
However, if you don't have future timestamps, just do:
where timestamp >= timestamp(date_sub(now(), interval 5 minute))
PHP MySQL select rows where date time value within the last n minutes
Use this query (There's no need to involve PHP date functions, when MySQL has them built in. See DATE_SUB()
for more info):
SELECT *
FROM tablename
WHERE Timestamp > DATE_SUB(NOW(), INTERVAL 49 MINUTE)
SQL: select date rows that contain specific hour and minute
Q: How can I select all the rows that contain 05:05 as the hour and minute in SQL?
A: For MySQL, look in the MySql Date and Time functions. There, you'll find Extract().
You can use it as follows:
https://www.w3schools.com/sql/func_mysql_extract.asp
Extract the minute from a datetime:
SELECT EXTRACT(MINUTE FROM "2017-06-15 09:34:21");
This assumes that you're storing the column as a "Date" type.
Different RDBMS vendors have different Date/Time functions. You'll have to read the documentation and experiment to determine which syntax to use for your particular DB vendor and your particular table schema.
How to select all records that are 10 minutes within current timestamp in MySQL?
The most efficient way would be to compare your timestamp (and only timestamp, without using any functions on it) to an expression that can be calculated as constant for every row, this way mysql can use index defined on your timestamp column.
SELECT * FROM myTable
WHERE last_seen >= NOW() - INTERVAL 10 MINUTE
You can always try EXPLAIN SELECT ...
to see if an index can be used to find rows satisfying your WHERE condition without the need to check every row in the table.
SELECT rows with time 30 minutes or less?
WHERE column >= DATEADD(mi, -30, GETDATE())
Select rows from a column within the first 45 minutes of the past 1 hour
Something like:
time >= DATE_SUB(NOW(),INTERVAL 1 HOUR) AND
time <= DATE_SUB(NOW(),INTERVAL 15 MINUTE)
15
can be changed to 20
if you like to have the first 40 minutes (question body text) instead of 45 minutes (question title).
-edit-
In your new situation, I understand you want to have a moving window of 1 hour for the cron job, so you could do that like this:
time >= DATE_SUB(NOW(),INTERVAL 75 MINUTE) AND
time <= DATE_SUB(NOW(),INTERVAL 15 MINUTE)
However, this is dangerous because your job will have to run in exactly the right time otherwise you will have misses anyway. It's better to flag the items you have processed. In the query you can exclude the items that have been processed before. This way, you won't risk processing an item twice. If the cron has stopped for some reason, you can just run it again to process all unprocessed items.
MySQL select all rows from last month until (now() - 1 month), for comparative purposes
You can get the first of the month, by calculating the last_day of the month before and add one day. It is awkward, but I think it is better than formatting a date as string and use that for calculation.
select
*
from
yourtable t
where
/* Greater or equal to the start of last month */
t.date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY) and
/* Smaller or equal than one month ago */
t.date <= DATE_SUB(NOW(), INTERVAL 1 MONTH)
Related Topics
Remove Decimal Values Using SQL Query
Finding the Highest Average Salary in SQL
Insert Value into Table If Conidition Is Met At Least One Time
What's the Best Way to Capitalise the First Letter of Each Word in a String in SQL Server
Multiplying Two Columns in SQL Server
How to Directly View Blobs in MySQL Workbench
How to Select an Empty Result Set
Postgresql Delete Multiple Rows from Multiple Tables
How to Insert Null into the Datetime Coulmn Instead 1900-01-01 00:00:00.000 in SQL Server
How to Execute a Stored Procedure Once for Each Row Returned by Query
How to Calculate Age (In Years) Based on Date of Birth and Getdate()
Extract Number from String With Oracle Function
How to Get Column Name Based on Row Value in SQL Server
Query to Get All Those Names of Employees,Who Have 'A' as Their Middle Character in Their Name
How to Close Idle Connections in Postgresql Automatically
Removing Leading Zeroes from a Field in a SQL Statement