SQLite Forum

Calculating Trip Duration in Minutes?
Login

Calculating Trip Duration in Minutes?

(1) By David Jackson (davidjackson) on 2021-08-04 19:35:21 [link] [source]

First A big thanks to Richard Hipp for the answer my original trip duration post. One small clarification, If I want to calculate trip duration in minutes would I use something like this:

CREATE TABLE detail AS SELECT start_lat,start_lng, rideable_type, member_casual, started_at,ended_at, (julianday(ended_at)-julianday(started_at))*3600 as duration FROM t1 WHERE duration >0

TX, David

(2) By Ryan Smith (cuz) on 2021-08-04 19:49:48 in reply to 1 [link] [source]

"Julianday" is a representation/measurement in days or fractions thereof.

So there are 24 hours in a day, 1440 minutes in a day and 86400 seconds in the average day.

If you have a factor in "days" (or fractions thereof) then to translate it to hours requires you multiplying it by 24, for minutes it is * 1440 and for seconds it is * 86400.

(5) By Keith Medcalf (kmedcalf) on 2021-08-04 20:12:18 in reply to 2 [link] [source]

So there are 24 hours in a day, 1440 minutes in a day and 86400 seconds in the average day.

This is incorrect.

There are EXACTLY 60 seconds in a minute, EXACTLY 60 minutes in an hour, and EXACTLY 24 hours in a day (EXACTLY 1440 minutes in a day and EXACTLY 86400 seconds in a day). EXACTLY. ALWAYS. EVERYTIME. WITHOUT EXCEPTION.

This indeed applies for the average day because it applies exactly to every day. Since every day is exactly always 86400 seconds long, the average day is also exactly 86400 seconds long, however, there is no day that is any length OTHER THAN 86400 seconds exactly.

Only the co-ordinated time is loosey goosey because it is based on an approximation of the length of second holding the duration constant, rather than the length of a second being based on being exactly 1/86400 of a day (and thus variable in length).

(6) By Larry Brasfield (larrybr) on 2021-08-04 20:23:27 in reply to 5 [link] [source]

There are EXACTLY 60 seconds in a minute, EXACTLY 60 minutes in an hour, and EXACTLY 24 hours in a day (EXACTLY 1440 minutes in a day and EXACTLY 86400 seconds in a day). EXACTLY. ALWAYS. EVERYTIME. WITHOUT EXCEPTION.

How does this strong assertion interact with the possibility of leap seconds?

(10) By Keith Medcalf (kmedcalf) on 2021-08-04 22:28:02 in reply to 6 [link] [source]

It does not.

Leap seconds are artifices which are inserted in order that the "not quite actual second" (called a UTC second -- notice this is a coordinated time interval, meaning an approximation, not an actual time) can be periodically synchronized with reality.

(11) By Larry Brasfield (larrybr) on 2021-08-04 22:50:47 in reply to 10 [source]

I'm finding it difficult to approach your certainty on this. By convention, considering only UTC, leap seconds are inserted between days. This means that, for UTC, there is no conflict between your EXACTLY assertions and the occasional leap second insertion. But according to Wikipedia on this, leap seconds are inserted "simultaneously" (in the everyday sense) across the world. This would mean that for timezones other than GMT, some days will be a little longer than 60^2 * 24 seconds. At Wikipedia's article on Julian days, this issue is touched upon thusly: "Time intervals calculated from differences of Julian Dates specified in non-uniform time scales, such as UTC, may need to be corrected for changes in time scales (e.g. leap seconds)." I doubt that the problem is avoided by using timezones other than UTC.

I suppose we could agree that Julian day calculations are entirely independent of leap seconds, by fiat, then pronounce that differences between Julian days are to be given astronomical accuracy by a post-adjustment process. Still, I think an asterisk may be needed in your set of EXACTLY assertions as applied to the present thread's nominal topic.

As for "leap seconds are artifices": Yes, as is quantified time in general.

(12) By Keith Medcalf (kmedcalf) on 2021-08-04 23:02:30 in reply to 11 [link] [source]

You said it yourself. UTC is a non-uniform time scale. "Actual Time" (as in UT1, for example) is, by contrast, a uniform time scale.

SQLite3 (and most things) use a uniform time scale. However, there is a non-uniform scale called UTC which is used to approximate reality. Since UTC only approximates reality, it must be "corrected by artifice" periodically so that the imaginary aligns with the reality, and these "artifices of correction" are called leap seconds.

Leap seconds only exist to make the imaginary consistent with the reality.

(13) By anonymous on 2021-08-05 15:18:44 in reply to 12 [link] [source]

Atomic clocked time UTC versus sun/earth rotation „natural“ times GMT. They should be the same but get slowly out of sync. That is why the more precise UTC needs to be corrected because the earth rotation cannot be changed.

For normal calculations during a humans livetime the seconds delta do not matter.

(19) By Stephan (stephancb) on 2022-06-19 16:25:51 in reply to 12 [link] [source]

If the timestamps have been automatically generated via any clock which is synchronized via an internet protocol (NTP, ...), then they are UTC not UT1. The difference can by up to 900 ms. And there is the very, very remote possibility that a leap second has occurred between two timestamps.

(7) By Ryan Smith (cuz) on 2021-08-04 20:33:19 in reply to 5 [link] [source]

This is incorrect... EXACTLY...

Yes, I am well aware of that. It was an ill-formed joke, I have a habit of referring to absolutes by using ironic language such as "average" or "standard" or "government issue".

I was going to continue tongue-in-cheek about how there are many types of days and UT1 vs. UTC and all the astronomy days, but that would be disingenuous as that is not what I intended, my meaning was strictly to time as understood by the Julianday calculation, which is absolute in dayspan.

I realised after your reply that not only was my intent not obvious, it might well be misleading to someone else reading this, and for that reason I apologize and will watch my mouth on this in most of the multiple possible futures.

Do not fear though, I am rather decisively on your side of that fence. ;)

(14) By anonymous on 2021-08-05 19:17:56 in reply to 5 [link] [source]

Only the co-ordinated time is loosey goosey because it is based on an approximation of the length of second holding the duration constant, rather than the length of a second being based on being exactly 1/86400 of a day (and thus variable in length).

In other words, multiplying a difference in Julian day numbers by 86400 will give you seconds, but they will be UT1 seconds, tied to the rotation of the Earth, not SI seconds, tied to the atomic clocks. Since the difference here is only a few leap seconds, it's probably not important.

So it goes.

(15) By tom (younique) on 2021-08-13 08:35:12 in reply to 5 [link] [source]

There are EXACTLY 60 seconds in a minute, EXACTLY 60 minutes in an hour, and EXACTLY 24 hours in a day (EXACTLY 1440 minutes in a day and EXACTLY 86400 seconds in a day). EXACTLY. ALWAYS. EVERYTIME. WITHOUT EXCEPTION.

Sorry, but this was only true in former days where a second was defined as 1/86400th of a day (based on rotation of earth). Nowadays, physically, a second is defined on Cs-133 (see SI unit system) which is approximately, but not exactly, coupled to earth rotation. So while your definition is, of course, correct for every-day use, physically is it wrong.

(16) By Harald Hanche-Olsen (hanche) on 2021-08-13 16:33:16 in reply to 15 [link] [source]

If you did read the whole discussion thread here, you should be aware that this is already known to the participants. The statement is true, however, about the convenient fiction that is sometimes known as POSIX time, as well as the timescale used by SQLite itself. That these timescales ignore leap seconds is well known already.

(17) By Keith Medcalf (kmedcalf) on 2021-08-13 20:19:48 in reply to 16 [link] [source]

The root of the problem, of course, is that different things have the same name, and there is a difficulty in determining which one of the two different things being spoken of is being referred to.

A "second" is 1/86400 of a day.

A "day" is defined as the period of time between a specific point on the surface of the earth having the sun directly overhead, and then the earch "rotating on its axis" such that the same point "goes around" and is once again has the sun directly overhead.

A "year" is defined as the period of time between a specific point on the earth having the sun directly overhead AND the earth having the same "rotation angle" around the sun.

Then came the invention of something called a "BoodleFuggle" which was pretty close to the length of a "second".

Unfortunately, additional "BoodleFuggle's" (leap boodlefuggle's) have to be inserted/deleted from time to time in order to make the "ascending count of BoodleFuggle's" be the same as the tally of "seconds".

This would all be quite straightforward, except that some nitwit decided to call the new BoodleFuggle's "seconds" (even though they are not).

Almost everything does time calculations using "seconds" even though they are really using boodlefuggle's, and the boodlefuggler's keep on thinking that boodlefuggle's are seconds, when they are not.

(3) By Larry Brasfield (larrybr) on 2021-08-04 19:50:18 in reply to 1 [link] [source]

The subexpression, (julianday(ended_at)-julianday(started_at)), gives a number of days. Conversion of days to minutes would require multiplication by the number of minutes a day. To a very close approximation, that is 24*60 (rather than 3600).

(8) By David Jackson (davidjackson) on 2021-08-04 21:05:11 in reply to 3 [link] [source]

Larry, Thanks for you reply.

David

(4.1) By ThanksRyan on 2021-08-04 20:18:38 edited from 4.0 in reply to 1 [link] [source]

(julianday(ended_at)-julianday(started_at))*3600

I do the number of minutes in a day: 1440. If it's less than a day, you'll get a decimal number less than 1.

(9) By David Jackson (davidjackson) on 2021-08-04 21:05:59 in reply to 4.1 [link] [source]

Ryan, Thanks for your reply.

David

(18.1) Originally by ineswww with edits by Stephan Beal (stephan) on 2022-06-19 14:33:40 from 18.0 in reply to 1 [link] [source]

Deleted