SQL Select data in the last 1 minutes

Question

i am currently practicing my SQL skills. I wanted to get all data in the past 1 minutes.

The query is SELECT * FROM menfesses WHERE created_at >= NOW() - INTERVAL 1 MINUTE; But somehow, it returns all data.

I have also use date_add approach and nothing works

What did i do wrong? Thanks

Query Result

The server time is 22.30. so it should not be within 1 minute — farhandika zahrir, Feb 09 '23 at 15:33
Wrap everything on the right of the >= in ()’s. Maybe it’s doing >= now which not much will be then subtracting the 1 minute — xQbert, Feb 09 '23 at 15:50

nbk · Answer 1 · 2023-02-09T15:51:54.733

0

The last five minutes must be select with a BETWEEN.

Also testing you should add

SELECT created_at, Now() FROM menfesses WHERE created_at BETWEEN NOW() - INTERVAL 5 MINUTE AND NOW()

So that have a chance to debug it correctly

Between would only give you the correct data, as your test server could have dates later then now

Edit

a fiddle demonstrates my point https://dbfiddle.uk/c5Jlko65

You maybe off on the system you have

edited Feb 09 '23 at 15:51

answered Feb 09 '23 at 15:43

nbk

45,398
8
30
47

tried to use it, somehow now it always returns 0 table. i tried to change it to 60 min still 0 table – farhandika zahrir Feb 09 '23 at 15:47
then run the code without the where clause and see what you got, times are always tricky – nbk Feb 09 '23 at 15:48

score 0 · Accepted Answer · answered Feb 09 '23 at 15:47

0

Probably your server time that's not what you think it is.

This work with a 5 min laps.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE t1
    (`c_date` datetime)
;
    
INSERT INTO t1
    (`c_date`)
VALUES
    (NOW() - INTERVAL 30 MINUTE),
    (NOW() - INTERVAL 2 MINUTE),
    (NOW() - INTERVAL 1 MINUTE)
;

Query 1:

SELECT *,NOW() FROM t1 WHERE c_date >= NOW() - INTERVAL 5 MINUTE

Results:

|               c_date |                NOW() |
|----------------------|----------------------|
| 2023-02-09T15:44:05Z | 2023-02-09T15:46:20Z |
| 2023-02-09T15:45:05Z | 2023-02-09T15:46:20Z |

answered Feb 09 '23 at 15:47

Blag

5,818
2
22
45

Are there a way to patch the server current date with my inserted date? I've set the timezone to Jakarta and MySQL is still wrong – farhandika zahrir Feb 09 '23 at 15:58
@farhandikazahrir you should take a look at https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc or other subject like that. 2 points : 1) is your server on the good time ? 2) are the date you put in your table in the good time-zone. – Blag Feb 09 '23 at 16:04

SQL Select data in the last 1 minutes

2 Answers2

Edit