How to get latitude and longitude on current timestamp? [on hold]
up vote
0
down vote
favorite
I have a database with a list of timestamps and their corresponding latitudes and longitudes. I am trying to get the longitude and latitude of the most recent timestamp in the database. Here I am able to get the most recent timestamp but my latitude and longitude are not updated and always returns the value of the first timestamp. How do I get it to return from the most recent timestamp?
SELECT delivery.vehicle_id, max(timestamp), latitude, longitude from test
inner join delivery on delivery.vehicle_id = test.vehicle_id
where DATE_FORMAT(timestamp,'%H:%i:%s') <=
DATE_FORMAT(CURRENT_TIMESTAMP,'%H:%i:%s')
group by delivery.vehicle_id
Desired output: For example latest timestamp for vehicle id 4034 is 13:20 and for vehicle id it is 13:05 so it should get latitude = 22.29098 and longitude = 114.199942 for vehicle id 4034 and for vehicle id 4035 it should get latitude = 22.279973 and longitude = 114.188513. The output needs to be latest latitude and longitude for all vehicles not just one.
delivery table
vehicle_id start_time end_time
4034 11/1/2014 9:00 11/1/2014 17:00
4035 11/1/2014 8:00 11/1/2014 16:00
test table
timestamp vehicle_id latitude longitude
11/1/2014 0:00 4034 22.283861 114.146215
11/1/2014 3:59 4034 22.292902 114.200619
11/1/2014 8:49 4034 22.28557 114.192797
11/1/2014 13:20 4034 22.29098 114.199942
11/1/2014 13:23 4034 22.290495 114.196016
11/1/2014 0:08 4035 22.293131 114.200671
11/1/2014 6:02 4035 22.293043 114.200737
11/1/2014 9:35 4035 22.278188 114.153437
11/1/2014 13:05 4035 22.279973 114.188513
11/1/2014 14:33 4035 22.277418 114.17543
mysql phpmyadmin
asked Nov 10 at 13:53
user3435500
54
put on hold as unclear what you're asking by Tim Biegeleisen, Hovercraft Full Of Eels, Pearly Spencer, Vega, sideshowbarker Nov 10 at 21:34
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
|
show 5 more comments
up vote
0
down vote
favorite
I have a database with a list of timestamps and their corresponding latitudes and longitudes. I am trying to get the longitude and latitude of the most recent timestamp in the database. Here I am able to get the most recent timestamp but my latitude and longitude are not updated and always returns the value of the first timestamp. How do I get it to return from the most recent timestamp?
SELECT delivery.vehicle_id, max(timestamp), latitude, longitude from test
inner join delivery on delivery.vehicle_id = test.vehicle_id
where DATE_FORMAT(timestamp,'%H:%i:%s') <=
DATE_FORMAT(CURRENT_TIMESTAMP,'%H:%i:%s')
group by delivery.vehicle_id
Desired output: For example latest timestamp for vehicle id 4034 is 13:20 and for vehicle id it is 13:05 so it should get latitude = 22.29098 and longitude = 114.199942 for vehicle id 4034 and for vehicle id 4035 it should get latitude = 22.279973 and longitude = 114.188513. The output needs to be latest latitude and longitude for all vehicles not just one.
delivery table
vehicle_id start_time end_time
4034 11/1/2014 9:00 11/1/2014 17:00
4035 11/1/2014 8:00 11/1/2014 16:00
test table
timestamp vehicle_id latitude longitude
11/1/2014 0:00 4034 22.283861 114.146215
11/1/2014 3:59 4034 22.292902 114.200619
11/1/2014 8:49 4034 22.28557 114.192797
11/1/2014 13:20 4034 22.29098 114.199942
11/1/2014 13:23 4034 22.290495 114.196016
11/1/2014 0:08 4035 22.293131 114.200671
11/1/2014 6:02 4035 22.293043 114.200737
11/1/2014 9:35 4035 22.278188 114.153437
11/1/2014 13:05 4035 22.279973 114.188513
11/1/2014 14:33 4035 22.277418 114.17543
mysql phpmyadmin
asked Nov 10 at 13:53
user3435500
54
put on hold as unclear what you're asking by Tim Biegeleisen, Hovercraft Full Of Eels, Pearly Spencer, Vega, sideshowbarker Nov 10 at 21:34
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Welcome to Stack Overflow! Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 10 at 13:59
1
Please post the table structure for both tables. And sample data would really help your question.
– Tim Biegeleisen
Nov 10 at 14:05
Do you have access to latest version of MySQL (version 8.0.2 and above) ?
– Madhur Bhaiya
Nov 10 at 15:03
Also, add the expected output to your question (based on the given sample data).
– Madhur Bhaiya
Nov 10 at 15:03
@MadhurBhaiya I included my desired output in the description
– user3435500
Nov 10 at 15:26
|
show 5 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a database with a list of timestamps and their corresponding latitudes and longitudes. I am trying to get the longitude and latitude of the most recent timestamp in the database. Here I am able to get the most recent timestamp but my latitude and longitude are not updated and always returns the value of the first timestamp. How do I get it to return from the most recent timestamp?
SELECT delivery.vehicle_id, max(timestamp), latitude, longitude from test
inner join delivery on delivery.vehicle_id = test.vehicle_id
where DATE_FORMAT(timestamp,'%H:%i:%s') <=
DATE_FORMAT(CURRENT_TIMESTAMP,'%H:%i:%s')
group by delivery.vehicle_id
Desired output: For example latest timestamp for vehicle id 4034 is 13:20 and for vehicle id it is 13:05 so it should get latitude = 22.29098 and longitude = 114.199942 for vehicle id 4034 and for vehicle id 4035 it should get latitude = 22.279973 and longitude = 114.188513. The output needs to be latest latitude and longitude for all vehicles not just one.
delivery table
vehicle_id start_time end_time
4034 11/1/2014 9:00 11/1/2014 17:00
4035 11/1/2014 8:00 11/1/2014 16:00
test table
timestamp vehicle_id latitude longitude
11/1/2014 0:00 4034 22.283861 114.146215
11/1/2014 3:59 4034 22.292902 114.200619
11/1/2014 8:49 4034 22.28557 114.192797
11/1/2014 13:20 4034 22.29098 114.199942
11/1/2014 13:23 4034 22.290495 114.196016
11/1/2014 0:08 4035 22.293131 114.200671
11/1/2014 6:02 4035 22.293043 114.200737
11/1/2014 9:35 4035 22.278188 114.153437
11/1/2014 13:05 4035 22.279973 114.188513
11/1/2014 14:33 4035 22.277418 114.17543
mysql phpmyadmin
asked Nov 10 at 13:53
user3435500
54
I have a database with a list of timestamps and their corresponding latitudes and longitudes. I am trying to get the longitude and latitude of the most recent timestamp in the database. Here I am able to get the most recent timestamp but my latitude and longitude are not updated and always returns the value of the first timestamp. How do I get it to return from the most recent timestamp?
SELECT delivery.vehicle_id, max(timestamp), latitude, longitude from test
inner join delivery on delivery.vehicle_id = test.vehicle_id
where DATE_FORMAT(timestamp,'%H:%i:%s') <=
DATE_FORMAT(CURRENT_TIMESTAMP,'%H:%i:%s')
group by delivery.vehicle_id
Desired output: For example latest timestamp for vehicle id 4034 is 13:20 and for vehicle id it is 13:05 so it should get latitude = 22.29098 and longitude = 114.199942 for vehicle id 4034 and for vehicle id 4035 it should get latitude = 22.279973 and longitude = 114.188513. The output needs to be latest latitude and longitude for all vehicles not just one.
delivery table
vehicle_id start_time end_time
4034 11/1/2014 9:00 11/1/2014 17:00
4035 11/1/2014 8:00 11/1/2014 16:00
test table
timestamp vehicle_id latitude longitude
11/1/2014 0:00 4034 22.283861 114.146215
11/1/2014 3:59 4034 22.292902 114.200619
11/1/2014 8:49 4034 22.28557 114.192797
11/1/2014 13:20 4034 22.29098 114.199942
11/1/2014 13:23 4034 22.290495 114.196016
11/1/2014 0:08 4035 22.293131 114.200671
11/1/2014 6:02 4035 22.293043 114.200737
11/1/2014 9:35 4035 22.278188 114.153437
11/1/2014 13:05 4035 22.279973 114.188513
11/1/2014 14:33 4035 22.277418 114.17543
mysql phpmyadmin
mysql phpmyadmin
asked Nov 10 at 13:53
user3435500
54
asked Nov 10 at 13:53
user3435500
54
edited Nov 10 at 14:59
asked Nov 10 at 13:53
user3435500
54
asked Nov 10 at 13:53
user3435500
54
asked Nov 10 at 13:53
user3435500
54
54
put on hold as unclear what you're asking by Tim Biegeleisen, Hovercraft Full Of Eels, Pearly Spencer, Vega, sideshowbarker Nov 10 at 21:34
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
put on hold as unclear what you're asking by Tim Biegeleisen, Hovercraft Full Of Eels, Pearly Spencer, Vega, sideshowbarker Nov 10 at 21:34
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Welcome to Stack Overflow! Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 10 at 13:59
1
Please post the table structure for both tables. And sample data would really help your question.
– Tim Biegeleisen
Nov 10 at 14:05
Do you have access to latest version of MySQL (version 8.0.2 and above) ?
– Madhur Bhaiya
Nov 10 at 15:03
Also, add the expected output to your question (based on the given sample data).
– Madhur Bhaiya
Nov 10 at 15:03
@MadhurBhaiya I included my desired output in the description
– user3435500
Nov 10 at 15:26
|
show 5 more comments
Welcome to Stack Overflow! Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 10 at 13:59
1
Please post the table structure for both tables. And sample data would really help your question.
– Tim Biegeleisen
Nov 10 at 14:05
Do you have access to latest version of MySQL (version 8.0.2 and above) ?
– Madhur Bhaiya
Nov 10 at 15:03
Also, add the expected output to your question (based on the given sample data).
– Madhur Bhaiya
Nov 10 at 15:03
@MadhurBhaiya I included my desired output in the description
– user3435500
Nov 10 at 15:26
Welcome to Stack Overflow! Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 10 at 13:59
Welcome to Stack Overflow! Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 10 at 13:59
1
1
Please post the table structure for both tables. And sample data would really help your question.
– Tim Biegeleisen
Nov 10 at 14:05
Please post the table structure for both tables. And sample data would really help your question.
– Tim Biegeleisen
Nov 10 at 14:05
Do you have access to latest version of MySQL (version 8.0.2 and above) ?
– Madhur Bhaiya
Nov 10 at 15:03
Do you have access to latest version of MySQL (version 8.0.2 and above) ?
– Madhur Bhaiya
Nov 10 at 15:03
Also, add the expected output to your question (based on the given sample data).
– Madhur Bhaiya
Nov 10 at 15:03
Also, add the expected output to your question (based on the given sample data).
– Madhur Bhaiya
Nov 10 at 15:03
@MadhurBhaiya I included my desired output in the description
– user3435500
Nov 10 at 15:26
@MadhurBhaiya I included my desired output in the description
– user3435500
Nov 10 at 15:26
|
show 5 more comments
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
For an approach like this one, you first need to made a subquery with the latest timestamps for every vehicle_id, and then join this subquery on the proper columns for get the rest of the information (i.e, the related latitude and longitude):
SELECT
t.vehicle_id,
t.timestamp,
t.latitude,
t.longitude
FROM
test AS t
INNER JOIN
(SELECT
vehicle_id, MAX(timestamp) AS maxT
FROM
test
GROUP BY
vehicle_id) AS max_log ON max_log.vehicle_id = t.vehicle_id
AND max_log.maxT = t.timestamp
Note this will only works good if the timestamp data is normalized, like on next sample, or if you use one of the available MySQL types available for this: DATETIME or TIMESTAMP.
test table
timestamp vehicle_id latitude longitude
11/01/2014 00:00 4034 22.283861 114.146215
11/01/2014 03:59 4034 22.292902 114.200619
11/01/2014 08:49 4034 22.28557 114.192797
11/01/2014 13:20 4034 22.29098 114.199942
11/01/2014 13:23 4034 22.290495 114.196016
11/01/2014 00:08 4035 22.293131 114.200671
11/01/2014 06:02 4035 22.293043 114.200737
11/01/2014 09:35 4035 22.278188 114.153437
11/01/2014 13:05 4035 22.279973 114.188513
11/01/2014 14:33 4035 22.277418 114.17543
You can play with a working example on next link:
DB Fiddle Example
answered Nov 10 at 18:12
D. Smania
2,4301321
Hi this is super helpful thank you so much!
– user3435500
Nov 11 at 2:09
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
For an approach like this one, you first need to made a subquery with the latest timestamps for every vehicle_id, and then join this subquery on the proper columns for get the rest of the information (i.e, the related latitude and longitude):
SELECT
t.vehicle_id,
t.timestamp,
t.latitude,
t.longitude
FROM
test AS t
INNER JOIN
(SELECT
vehicle_id, MAX(timestamp) AS maxT
FROM
test
GROUP BY
vehicle_id) AS max_log ON max_log.vehicle_id = t.vehicle_id
AND max_log.maxT = t.timestamp
Note this will only works good if the timestamp data is normalized, like on next sample, or if you use one of the available MySQL types available for this: DATETIME or TIMESTAMP.
test table
timestamp vehicle_id latitude longitude
11/01/2014 00:00 4034 22.283861 114.146215
11/01/2014 03:59 4034 22.292902 114.200619
11/01/2014 08:49 4034 22.28557 114.192797
11/01/2014 13:20 4034 22.29098 114.199942
11/01/2014 13:23 4034 22.290495 114.196016
11/01/2014 00:08 4035 22.293131 114.200671
11/01/2014 06:02 4035 22.293043 114.200737
11/01/2014 09:35 4035 22.278188 114.153437
11/01/2014 13:05 4035 22.279973 114.188513
11/01/2014 14:33 4035 22.277418 114.17543
You can play with a working example on next link:
DB Fiddle Example
answered Nov 10 at 18:12
D. Smania
2,4301321
Hi this is super helpful thank you so much!
– user3435500
Nov 11 at 2:09
add a comment |
up vote
1
down vote
accepted
For an approach like this one, you first need to made a subquery with the latest timestamps for every vehicle_id, and then join this subquery on the proper columns for get the rest of the information (i.e, the related latitude and longitude):
SELECT
t.vehicle_id,
t.timestamp,
t.latitude,
t.longitude
FROM
test AS t
INNER JOIN
(SELECT
vehicle_id, MAX(timestamp) AS maxT
FROM
test
GROUP BY
vehicle_id) AS max_log ON max_log.vehicle_id = t.vehicle_id
AND max_log.maxT = t.timestamp
Note this will only works good if the timestamp data is normalized, like on next sample, or if you use one of the available MySQL types available for this: DATETIME or TIMESTAMP.
test table
timestamp vehicle_id latitude longitude
11/01/2014 00:00 4034 22.283861 114.146215
11/01/2014 03:59 4034 22.292902 114.200619
11/01/2014 08:49 4034 22.28557 114.192797
11/01/2014 13:20 4034 22.29098 114.199942
11/01/2014 13:23 4034 22.290495 114.196016
11/01/2014 00:08 4035 22.293131 114.200671
11/01/2014 06:02 4035 22.293043 114.200737
11/01/2014 09:35 4035 22.278188 114.153437
11/01/2014 13:05 4035 22.279973 114.188513
11/01/2014 14:33 4035 22.277418 114.17543
You can play with a working example on next link:
DB Fiddle Example
answered Nov 10 at 18:12
D. Smania
2,4301321
Hi this is super helpful thank you so much!
– user3435500
Nov 11 at 2:09
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
For an approach like this one, you first need to made a subquery with the latest timestamps for every vehicle_id, and then join this subquery on the proper columns for get the rest of the information (i.e, the related latitude and longitude):
SELECT
t.vehicle_id,
t.timestamp,
t.latitude,
t.longitude
FROM
test AS t
INNER JOIN
(SELECT
vehicle_id, MAX(timestamp) AS maxT
FROM
test
GROUP BY
vehicle_id) AS max_log ON max_log.vehicle_id = t.vehicle_id
AND max_log.maxT = t.timestamp
Note this will only works good if the timestamp data is normalized, like on next sample, or if you use one of the available MySQL types available for this: DATETIME or TIMESTAMP.
test table
timestamp vehicle_id latitude longitude
11/01/2014 00:00 4034 22.283861 114.146215
11/01/2014 03:59 4034 22.292902 114.200619
11/01/2014 08:49 4034 22.28557 114.192797
11/01/2014 13:20 4034 22.29098 114.199942
11/01/2014 13:23 4034 22.290495 114.196016
11/01/2014 00:08 4035 22.293131 114.200671
11/01/2014 06:02 4035 22.293043 114.200737
11/01/2014 09:35 4035 22.278188 114.153437
11/01/2014 13:05 4035 22.279973 114.188513
11/01/2014 14:33 4035 22.277418 114.17543
You can play with a working example on next link:
DB Fiddle Example
answered Nov 10 at 18:12
D. Smania
2,4301321
For an approach like this one, you first need to made a subquery with the latest timestamps for every vehicle_id, and then join this subquery on the proper columns for get the rest of the information (i.e, the related latitude and longitude):
SELECT
t.vehicle_id,
t.timestamp,
t.latitude,
t.longitude
FROM
test AS t
INNER JOIN
(SELECT
vehicle_id, MAX(timestamp) AS maxT
FROM
test
GROUP BY
vehicle_id) AS max_log ON max_log.vehicle_id = t.vehicle_id
AND max_log.maxT = t.timestamp
Note this will only works good if the timestamp data is normalized, like on next sample, or if you use one of the available MySQL types available for this: DATETIME or TIMESTAMP.
test table
timestamp vehicle_id latitude longitude
11/01/2014 00:00 4034 22.283861 114.146215
11/01/2014 03:59 4034 22.292902 114.200619
11/01/2014 08:49 4034 22.28557 114.192797
11/01/2014 13:20 4034 22.29098 114.199942
11/01/2014 13:23 4034 22.290495 114.196016
11/01/2014 00:08 4035 22.293131 114.200671
11/01/2014 06:02 4035 22.293043 114.200737
11/01/2014 09:35 4035 22.278188 114.153437
11/01/2014 13:05 4035 22.279973 114.188513
11/01/2014 14:33 4035 22.277418 114.17543
You can play with a working example on next link:
DB Fiddle Example
answered Nov 10 at 18:12
D. Smania
2,4301321
edited Nov 11 at 2:52
answered Nov 10 at 18:12
D. Smania
2,4301321
answered Nov 10 at 18:12
D. Smania
2,4301321
answered Nov 10 at 18:12
D. Smania
2,4301321
2,4301321
Hi this is super helpful thank you so much!
– user3435500
Nov 11 at 2:09
add a comment |
Hi this is super helpful thank you so much!
– user3435500
Nov 11 at 2:09
Hi this is super helpful thank you so much!
– user3435500
Nov 11 at 2:09
Hi this is super helpful thank you so much!
– user3435500
Nov 11 at 2:09
add a comment |
Welcome to Stack Overflow! Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 10 at 13:59
1
Please post the table structure for both tables. And sample data would really help your question.
– Tim Biegeleisen
Nov 10 at 14:05
Do you have access to latest version of MySQL (version 8.0.2 and above) ?
– Madhur Bhaiya
Nov 10 at 15:03
Also, add the expected output to your question (based on the given sample data).
– Madhur Bhaiya
Nov 10 at 15:03
@MadhurBhaiya I included my desired output in the description
– user3435500
Nov 10 at 15:26