CODEWITHSUNDEEP
mysqlsqldatabase
Fajar Donny Bachtiar
Fajar Donny Bachtiar

Reputation: 170

How to display data date MYSQL /2 hours

i have table in DB like this

dbFiddle

i want to display the data order by date / 2 hours

for example 

HF01245523 | 2019-11-17 06:01:05 | 1.10513 | 599
HF01245043 | 2019-11-17 04:01:06 | 1.10513 | 599
HF01244562 | 2019-11-17 02:00:06 | 1.10513 | 599

Is that possible?

I tried this query:

SELECT * FROM (
    SELECT MAX(tgl_harga) AS maxdate
    FROM tbl_dummy
    GROUP BY 
        YEAR(tgl_harga), 
        MONTH(tgl_harga), 
        WEEK(tgl_harga), 
        DAY(tgl_harga) 
) AS g 
INNER JOIN tbl_dummy ON g.maxdate = tbl_dummy.tgl_harga

But this query just display /day, I dont know to display records /2 hours.

Upvotes: 1

Views: 87

Answers (1)

GMB
GMB

Reputation: 222482

Building on your existing query, you can do date arithmetic. Consider:

SELECT t.* FROM (
    SELECT MAX(tgl_harga) AS maxdate
    FROM tbl_dummy
    GROUP BY 
        DATE(tgl_harga), 
        FLOOR(HOUR(tgl_harga) / 2)
) AS g 
INNER JOIN tbl_dummy t ON g.maxdate = t.tgl_harga
ORDER BY t.tgl_harga DESC

This gives you one record for each fixed 2 hour window, which is the latest record in that window (so: one record between 0h and 2h, one record between 2h and 4h, and so on). Note that this does not necessarily mean that the records in the resultset will have a 2 hour spacing between them.

In your demo on DB Fiddle, the query produces:

id_harga   | tgl_harga           |   harga | id_region
:--------- | :------------------ | ------: | :--------
HF01245523 | 2019-11-17 06:01:05 | 1.10513 | 599      
HF01245515 | 2019-11-17 05:59:07 | 1.10513 | 599      
HF01245035 | 2019-11-17 03:59:06 | 1.10513 | 599      
HF01244554 | 2019-11-17 01:57:06 | 1.10513 | 599      
HF01244082 | 2019-11-16 23:59:06 | 1.10513 | 599      
HF01243602 | 2019-11-16 21:59:06 | 1.10513 | 599      
HF01243114 | 2019-11-16 19:57:07 | 1.10513 | 599      
HF01242642 | 2019-11-16 17:59:06 | 1.10513 | 599      
HF01242162 | 2019-11-16 15:59:07 | 1.10513 | 599      
HF01241682 | 2019-11-16 13:59:06 | 1.10513 | 599

Upvotes: 1

Related Questions