Select Rows Within Last Complete Minute

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



Leave a reply



Submit