Calculating time interval for one day by joining tables
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am a beginner in Postgres. I'm still learning by mostly solving problems.
I have a PostgreSQL database with two tables: trips, which contains information about each trip a vehicle has taken, and gps_points, which contains the GPS points covered on each trip.
The trips table contains the columns [id, start_time, end_time, vehicle_id]. The gps_points table contains the columns [id, trip_id, latitude, longitude].
How do I write a query to select the GPS points (latitude and longitude) covered by vehicle number 4 on trips which were fully contained on the 1st January 2018 (i.e. both started and ended on that date).
sql postgresql
edited Nov 26 '18 at 21:20
a_horse_with_no_name
308k46469570
asked Nov 26 '18 at 18:34
Uthred RagnarsonUthred Ragnarson
11
add a comment |
I am a beginner in Postgres. I'm still learning by mostly solving problems.
I have a PostgreSQL database with two tables: trips, which contains information about each trip a vehicle has taken, and gps_points, which contains the GPS points covered on each trip.
The trips table contains the columns [id, start_time, end_time, vehicle_id]. The gps_points table contains the columns [id, trip_id, latitude, longitude].
How do I write a query to select the GPS points (latitude and longitude) covered by vehicle number 4 on trips which were fully contained on the 1st January 2018 (i.e. both started and ended on that date).
sql postgresql
edited Nov 26 '18 at 21:20
a_horse_with_no_name
308k46469570
asked Nov 26 '18 at 18:34
Uthred RagnarsonUthred Ragnarson
11
1
I think you have forgotten to ask a question. Sample data and desired results would also be a big help.
– Gordon Linoff
Nov 26 '18 at 18:36
the key parts of your question are not really distinct and reusable, please define code blocks and provide some data to represent it.
– Siavash Rostami
Nov 26 '18 at 18:43
add a comment |
I am a beginner in Postgres. I'm still learning by mostly solving problems.
I have a PostgreSQL database with two tables: trips, which contains information about each trip a vehicle has taken, and gps_points, which contains the GPS points covered on each trip.
The trips table contains the columns [id, start_time, end_time, vehicle_id]. The gps_points table contains the columns [id, trip_id, latitude, longitude].
How do I write a query to select the GPS points (latitude and longitude) covered by vehicle number 4 on trips which were fully contained on the 1st January 2018 (i.e. both started and ended on that date).
sql postgresql
edited Nov 26 '18 at 21:20
a_horse_with_no_name
308k46469570
asked Nov 26 '18 at 18:34
Uthred RagnarsonUthred Ragnarson
11
I am a beginner in Postgres. I'm still learning by mostly solving problems.
I have a PostgreSQL database with two tables: trips, which contains information about each trip a vehicle has taken, and gps_points, which contains the GPS points covered on each trip.
The trips table contains the columns [id, start_time, end_time, vehicle_id]. The gps_points table contains the columns [id, trip_id, latitude, longitude].
How do I write a query to select the GPS points (latitude and longitude) covered by vehicle number 4 on trips which were fully contained on the 1st January 2018 (i.e. both started and ended on that date).
sql postgresql
sql postgresql
edited Nov 26 '18 at 21:20
a_horse_with_no_name
308k46469570
asked Nov 26 '18 at 18:34
Uthred RagnarsonUthred Ragnarson
11
edited Nov 26 '18 at 21:20
a_horse_with_no_name
308k46469570
asked Nov 26 '18 at 18:34
Uthred RagnarsonUthred Ragnarson
11
edited Nov 26 '18 at 21:20
a_horse_with_no_name
308k46469570
edited Nov 26 '18 at 21:20
a_horse_with_no_name
308k46469570
edited Nov 26 '18 at 21:20
a_horse_with_no_name
308k46469570
308k46469570
asked Nov 26 '18 at 18:34
Uthred RagnarsonUthred Ragnarson
11
asked Nov 26 '18 at 18:34
Uthred RagnarsonUthred Ragnarson
11
asked Nov 26 '18 at 18:34
Uthred RagnarsonUthred Ragnarson
11
11
1
I think you have forgotten to ask a question. Sample data and desired results would also be a big help.
– Gordon Linoff
Nov 26 '18 at 18:36
the key parts of your question are not really distinct and reusable, please define code blocks and provide some data to represent it.
– Siavash Rostami
Nov 26 '18 at 18:43
add a comment |
1
I think you have forgotten to ask a question. Sample data and desired results would also be a big help.
– Gordon Linoff
Nov 26 '18 at 18:36
the key parts of your question are not really distinct and reusable, please define code blocks and provide some data to represent it.
– Siavash Rostami
Nov 26 '18 at 18:43
1
1
I think you have forgotten to ask a question. Sample data and desired results would also be a big help.
– Gordon Linoff
Nov 26 '18 at 18:36
I think you have forgotten to ask a question. Sample data and desired results would also be a big help.
– Gordon Linoff
Nov 26 '18 at 18:36
the key parts of your question are not really distinct and reusable, please define code blocks and provide some data to represent it.
– Siavash Rostami
Nov 26 '18 at 18:43
the key parts of your question are not really distinct and reusable, please define code blocks and provide some data to represent it.
– Siavash Rostami
Nov 26 '18 at 18:43
add a comment |
1 Answer
1
active
oldest
votes
This should do the trick:
select p.latitude
, p.longitude
from trips t
join gps_points p
on p.trip_id = t.id
where t.vehicle_id = 4
and date_trunc('day', t.start_time) = date '2018-01-01'
and date_trunc('day', t.end_time) = date '2018-01-01'
answered Nov 28 '18 at 0:30
SentinelSentinel
5,06011221
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53487101%2fcalculating-time-interval-for-one-day-by-joining-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This should do the trick:
select p.latitude
, p.longitude
from trips t
join gps_points p
on p.trip_id = t.id
where t.vehicle_id = 4
and date_trunc('day', t.start_time) = date '2018-01-01'
and date_trunc('day', t.end_time) = date '2018-01-01'
answered Nov 28 '18 at 0:30
SentinelSentinel
5,06011221
add a comment |
This should do the trick:
select p.latitude
, p.longitude
from trips t
join gps_points p
on p.trip_id = t.id
where t.vehicle_id = 4
and date_trunc('day', t.start_time) = date '2018-01-01'
and date_trunc('day', t.end_time) = date '2018-01-01'
answered Nov 28 '18 at 0:30
SentinelSentinel
5,06011221
add a comment |
This should do the trick:
select p.latitude
, p.longitude
from trips t
join gps_points p
on p.trip_id = t.id
where t.vehicle_id = 4
and date_trunc('day', t.start_time) = date '2018-01-01'
and date_trunc('day', t.end_time) = date '2018-01-01'
answered Nov 28 '18 at 0:30
SentinelSentinel
5,06011221
This should do the trick:
select p.latitude
, p.longitude
from trips t
join gps_points p
on p.trip_id = t.id
where t.vehicle_id = 4
and date_trunc('day', t.start_time) = date '2018-01-01'
and date_trunc('day', t.end_time) = date '2018-01-01'
answered Nov 28 '18 at 0:30
SentinelSentinel
5,06011221
answered Nov 28 '18 at 0:30
SentinelSentinel
5,06011221
answered Nov 28 '18 at 0:30
SentinelSentinel
5,06011221
answered Nov 28 '18 at 0:30
SentinelSentinel
5,06011221
5,06011221
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53487101%2fcalculating-time-interval-for-one-day-by-joining-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
I think you have forgotten to ask a question. Sample data and desired results would also be a big help.
– Gordon Linoff
Nov 26 '18 at 18:36
the key parts of your question are not really distinct and reusable, please define code blocks and provide some data to represent it.
– Siavash Rostami
Nov 26 '18 at 18:43