Android Question [RESOLVED] QUERY MYSQL

[MarcoRome]

Hi All.
I have this table:

Through this query, where 4200 are 70 minute:

SELECT

data_entrata,

FROM_UNIXTIME((UNIX_TIMESTAMP(data_entrata) DIV 4200) * 4200, '%d-%m-%Y %H:%i')

FROM

test

ORDER BY data_entrata

I wait for the column (the second one you see) to be populated with the same data if it falls within 70 minutes.
Example time 09:15 so I would expect all values up to 10:25 ( 9:15 + 70 min. ) to have the same value (always in the second column)

for the first group of lines that go from 09:15 to 10:20 I would have expected to see the same value, but I don't get this result.
The correct situation should be from:
09:15 to 10:20
and from 10:50 to 11:19

Any suggestions ?
Thank you

 

[Alexander Stolte]

 

[MarcoRome]

View attachment 136484

Right, sorry ?

 

[MarcoRome]

One thing i figured out that DIV does a DIV Integer division and then returns an integer.
Example if you execute this:

SELECT
data_entrata,
UNIX_TIMESTAMP(data_entrata),
FROM_UNIXTIME((UNIX_TIMESTAMP(data_entrata) DIV 4200) * 4200, '%d-%m-%Y %H:%i')
FROM
test
ORDER BY data_entrata

Have this result:

Now if you get 2 columns value and divide by 4200 you have this result:

1669450501 / 4200 = 397488,2145..... with DIV come only integer 397488
1669450512 / 4200 = 397488,2171.... with DIV come only integer 397488
and so until 4 record
1669453849 / 4200 = 397.489,0116....with DIV come only integer 397489
an so change....

Do you have any other ways to suggest to get a similar result ?

 

[Computersmith64]

I'm not exactly sure what you're trying to do here, but wouldn't using a WHERE clause be the way to go? So something along the lines of (in pseudo code):

SELECT data_entrata WHERE data_entrata >= reference time + 70 minutes.

- Colin.

 

[MarcoRome]

I'm not exactly sure what you're trying to do here, but wouldn't using a WHERE clause be the way to go? So something along the lines of (in pseudo code):

SELECT data_entrata WHERE data_entrata >= reference time + 70 minutes.

- Colin.

Thanks @Colin Evans for replying.
I need the second column because then I will use it in the GROUP BY clause.
I currently have the following clause. It works if it falls within an hour.

SELECT
COALESCE(COUNT(id_utente) OVER(),0) as totale
FROM test
WHERE id_utente = 1
AND id_corso = 52
GROUP BY DATE_FORMAT(data_entrata, '%d-%m-%Y %H')
LIMIT 1

To give you an example, if I have the following table

and I run the above query i will get the following result:

Because if you look at the times, they are between all 09, 10, 11, so each time is counted as 1, for a total of 3
My problem is that it is not divided into hours but into 70 minutes, therefore, as explained above, I will need to group them by 70 minutes like:

 

[MarcoRome]

Resolved in this way:

SET @minimo = (SELECT min(data_entrata) FROM test);
SET @incrementa = 1;

SELECT
data_entrata,
IF(data_entrata <  ADDDATE(@minimo, INTERVAL 70 MINUTE),
@incrementa,
@incrementa := @incrementa + 1) AS yy,
IF(data_entrata <  ADDDATE(@minimo, INTERVAL 70 MINUTE),
@minimo,
@minimo := data_entrata) AS XX
FROM
test
ORDER BY data_entrata;

SELECT @incrementa as risultato;