Android Question [RESOLVED] QUERY MYSQL

MarcoRome

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



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)




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
 
Last edited:
Sort by date Sort by votes

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:



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

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



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:

 
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;

 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…