Android Question [RESOLVED] QUERY MYSQL

MarcoRome

Expert
Licensed User
Longtime User
Hi All.
I have this table:

1669565197125.png


Through this query, where 4200 are 70 minute:

SQL:
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)

1669565242712.png



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


1669565292553.png


Any suggestions ?
Thank you
 
Last edited:

MarcoRome

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

PHP:
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:


1669565632730.png

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 ?
 
Upvote 0

Computersmith64

Well-Known Member
Licensed User
Longtime User
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):
B4X:
SELECT data_entrata WHERE data_entrata >= reference time + 70 minutes.

- Colin.
 
Upvote 0

MarcoRome

Expert
Licensed User
Longtime User
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):
B4X:
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.

SQL:
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

1669617652609.png


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

1669617827849.png


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:

1669618042461.png
 
Upvote 0

MarcoRome

Expert
Licensed User
Longtime User
Resolved in this way:

SQL:
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;

1669872436709.png
 
Upvote 0
Top