Hello. The following:

select 11.44 * '05:30'

returns 57.2 but the correct answer is 62.92

It seems I need to convert from hour format to a float (5.5 in this case). As I'm writing a program to track hours and money earned, any tips or best practices on saving start and end times for a shift would be appreciated!

The correct answer is 57.2, mathematically. There is no such operation as time multiplication in SQLite. The right operand is coerced to a number, which for your input string, ends at the colon.

You appear to want some sort of duration multiplication. You will find something(s) useful among the date and time functions for that purpose.

assuming:

• string timeworked in format ['HH:MM']
• float hourlyrate

hourlyrate * CAST((strftime(%s, timediff('00:00',timeworked)) as INT)/3600)

Explanation

td = timediff('00:00',timeworked) gives a human readable string - this step could be omitted in your example ('05:30') entering '05:30' as td but it could actually accept timeworked in other acceptable formats eg '1970-01-DD HH:MM' but then the first argument would have to be '1970-01-01 00:00'. using the longer format is probably better practice anyway

tds = strftime(%s, td) gives the timediff tds in seconds as a string

CAST(tds as INT) gives the INT number of seconds which you then divide by 3600 to get the decimal hours

Alternatively it is probably better and quicker to do:

hourlyrate * unixepoch(strftime(%s, timediff('1970-01-01 00:00','1970-01-01 '||timeworked))) where timeworked is HH:MM

|| concatenates '1970-01-01 ' with timeworked

Thanks but unfortunately my version of sqlite is pre the timediff function.

You can (or should be able to) use other functionality in that collection of datetime functions to synthesize a timediff function as an expression.

If you are a student, you should do this yourself to learn the subject matter.

If you are a programmer earning a wage or salary, you need to learn to do this sort of simple thing rather than begging real programmers to do it for you.

Sorry if that's harsh, but this is not what anybody would call a challenge who earns their keep by writing code.

Are you a moderator? If so, why did you not approve the solution I posted?

I am one of several moderators here. If one of your posts in this thread was rejected, I was not the one who did so.

At present, I see no posts in this thread by either (one of many known as) anonymous or by "simonh" which could be considered either a solution or an attempt or beginning of a solution. Nor are any such in the moderation queue. So, taking the implication of your 2nd question as a given, there are only a few reasons it would have been rejected. These are: (1) It was abusive; (2) It was profane; (3) It was wholly off-topic, as perceived by the moderator; or (4) It contained substantial commercial content. I am highly confident, to a near-certainty, that the other moderators would agree with this reasoning and its factual basis.

If one of your posts in this thread was rejected, I was not the one who did so.

To the best of my knowledge, the only rejected (by me) post in this thread was from an anonymous user who got rather snippy. The acceptable threshold for snippininess is considerably lower for anonymous users than it is for users posting non-anonymously.

Also, I'm pretty sure there was no "begging". I simply asked for tips.

... there was no "begging".

I reply, not to argue with the quoted assertion but to make some relevant facts clear to others.

The first definition that Merriam Webster has for "beg" is: "to ask for as a charity". They show a common usage of the word "charity" as "generosity and helpfulness especially toward the needy or suffering".

Many contributors to this forum, including me, perform this kind of charity by answering questions for those who show themselves to be in need of help. They are not paid for their effort, and I see its motivation as generally charitable. My first post in this thread was just such charity. My response was to provide a link to a well-written web page, where SQLite's date/time functions are fully described so that people can use them properly and confidently, along with a tip that they would be useful for the OP's purpose.

Another charitable contributor went further, providing a possibly complete solution¹ which was not suitable to the OP. The OP intimated that he still needed a solution, ignoring the fact that a variety of solutions could easily be devised once SQLite's date/time functions were comprehended.

At that point, I saw what looked like a homework problem given to a student of the kind most of us have seen -- eager to get somebody else to do the work that the homework is designed to induce so as to promote learning. I figured that any serious programmer would have looked at the date/time functions page by that time. And I perceived then that the OP was unwilling to invest the time needed to notice the unixepoch() function and its obvious utility for synthesizing a solution of the OP's presented problem. I believed then and continue to believe that SQLite users who need to deal with times and/or dates should be or become aware of the existence of those functions and have or gain at least some rudimentary understanding of what they do. So I saw no reason to not share this sentiment and encourage some learning that would be useful to somebody who appeared to be such a user (if not just a cheating student.)

What I did not see was something difficult enough to merit expending more of my time. I am dubious regarding the value of doing for others what they can do for themselves with a few tips and their own effort. I would prefer to say, "Here is a good book on fishing." rather than teach somebody how to fish or hand them some fish I bought or caught.² I tend to regard low willingness to expend some self-help effort as indicating a low valuation of the objective, making it worth little of my time or an utter waste of my time. And my patience is infinitesimal with people attending school on somebody else's dime who try to get others to do their homework.

1. ^{^} Whether the solution was complete would depend upon the range of durations that might be expressed in RE form as ^\d+:\d\d$.
2. ^{^} Some of my attitude about wasted charity comes from a year I spent while a poor student, working at near minimum wage. Every evening, while walking to the bus stop, I would encounter people pleading hunger and a need for "spare change". I routinely offered to buy them a meal then and there -- offers which were never accepted. I later learned that they generally collect more "change" per hour than I was paid then. My "charity" would have been a waste of their time.

select (unixepoch('05:30') - unixepoch('00:00')) / 3600.;

is a workable alternative (if you have unixepoch!) but as mentioned elsewhere it is worth doing at least a little reading and a lot of thinking. This is because date times will come and bite you when you least expect it.

Yes, I agree. That's why I was hoping for some best practices to avoid being bitten!

Best practice would be to save the start and finishing shift times in an sqlite recognised format (as mentioned by another poster) as a fully qualified date time in UTC.

Only then can you avoid various edge cases with any degree of confidence.

It also would avoid a lot of the text manipulation and date time gymnastics implicit in the proposed solutions (including my own).

sqlite> SELECT 11.44 * (SUBSTRING('05:30', 1, 2) + CAST(SUBSTRING('05:30', 4, 2) AS REAL)/60) AS amount;

amount
------
62.92

But really, you need to save "start and end times for a shift" in their own columns defined as one of the documented values supported by your (old) version of SQLite.

(another annon here)

I have not looked over all the replies, so I may repeat some of the advice (apologies if so).

Since your purpose is calculating 'money earned' you may wish to think about handling your calculations in precise, exact units (as in financial accounting).

That is to say:

Instead of trying to stuff the minutes into a fractional hour, and the cents into a fractional dollar, consider putting them into whole minutes, and whole cents. In short, consider arranging your data so the calculations are always exact.

1. handle currency in base units (eg 1144 could be $11.44 USD).
2. handle time in base units (eg 330 could be 05:30, or 5 hours, 30 minutes).
3. you can of course generate the display format as desired in your app.

Thus, you could have 330 * 1144 for your example which gives an exact calculation.

Why? You may wish to be able to aggregate rows and not have the floating point number rounded (eg you sum up millions of rows).

pro tip: consider the information you are handling, not just the nomenclature/display format of your information.

Not trying to be difficult, but this will only be exact if the contract is written as $x/min. If however it is written as $x/whole hour completed, $x/whole hour or part thereof, $x/half hour completed, $x/half hour or part thereof etc, rounding and aggregation errors will still occur. As a result it may be neccessary to aggregate on data segmented on time and employee/contractor/contract.

pro tip: understand the client's needs BEFORE starting to write code - even when the client is yourself (painful experience)

Actually, no. You are making several errors.

What you describe is a completely different base unit than specified by OP. In your case your base unit moved from HH:MM of the original post to something else entirely (an opaque measurement of rate not based on HH:MM).

If we look closely at the title of the thread as a clue, we see "Calculating using times (HH:MM) format".

Whatever fractional hour you wish to use, the specification of OP calls for HH:MM as the expression of time, thus representing the HH:MM in it's own base unit would be a minute. In the example of OP it was stated as 05:30, as in 5 hours and 30 minutes.

One can of course define entirely different problems to solve, such as orbits, etc., but the question was how to calculate based on HH:MM and a dollars/cents (presumably USD, though storing in base currency also makes multi currency more manageable ). Whatever the starting and ending times may be, they must result in HH:MM to meet the OP specification. That is NOT done with representing time as a float.

You are free to make up whatever extra criteria you wish, however William of Occam would frown upon it (eg. we forgot to also handle the space aliens that tinker with the calculation as well). However OP has "select 11.44 * '05:30'", and asks how to handle HH:MM calculations. The answer is to consider the base units of both currency and time (in this case it's stated as HH:MM). As such, I'll stand by my recommendations to the OP.

Your objection appears to come down to "but this only solves the problem as stated, not some other different problem". I'll have to pass on that 'debate'.

I cannot agree with anybody about how to achieve exactitude when the only example we have uses 11.44 (a floating point literal which cannot be equal to the number presented) as an example. If we attempt to translate that tea leaf into a specification, we would be obliged to say that errors resembling floating point ULPs are acceptable.

( annon22 poster here)

LOL, yes agree about the OP example (I thought It showed OP's general level of understanding)

I would agree with that limitation if all we had was 11.44, however the OP says they are doing time and money. I think it was reasonable to couple the HH:MM to time and the 11.44 to money. If we accept that assertion then we are representing a monetary value. Since the OP asked for best practice, I would advise that they consider making perfect and exact calculations as it will allow the software to pass an audit much easier than fussing about with epsilon s, etc. (one can still use guard digits for a belt and suspenders approach ).

As to time, an HH:MM value is an exact value as well. How one arrives at the HH:MM value is another matter. Certainly if the HH:MM is just a general time value then the base unit could/should be different.

The point of my 'consider their base units' post was to get the OP to think in terms of the information they are handling rather than its presentation format. Depending on the application they very well may be able to have exact calculations.

Where I could have elaborated further is that it is perfectly ok to do whatever calculations one wishes for interim values but when we put that value in an account as a monetary value it is best as an exact value. In this particular case the calculation can be performed with exact precision. I have made similar points to the late Keith Medcalf (I am sure I am not the only one who misses his enthusiasm and intelligence)., namely, accounting is a counting exercise.

Bottom line is that OP presented a problem ($ x HH:MM ) that can be made easier by using base units, in this case minutes and cents. The spec for start/end to HH:MM was so vague I ignored it given it was stated they had HH:MM (by some magic I presume).

In any event that was my reasoning.

Regards

Pedant alert!!

Bottom line is that OP presented a problem ($ x HH:MM )

Actually the problem was ($/hour * HH:MM)

Of course since time equals money that just resolves to an amount whose units could be time or $ (if you have a fixed view of the value of time)

Just kidding :-)

To a first approximation I'm sympathetic to this point of view, while not sharing it.

However, long experience has shown that decomposing XY problems (which this is certainly an example of) saves time and avoids more complex issues in the longer term.

Plus, the OP specifically asked for advice regarding best practice, as well as tips.

This is not relying on floating points to represent either currency or fractional time periods.

So I think you're partly right, but mostly wrong.

But I fear a further lurch in the direction of off-topic so I will leave it there (but thoughtfully consider responses).

fair enough, particularly on the vague time side.

It is less xy if viewed timediff renders whole HH:MM. OP is simply to vague to know if the HH:MM can be relaxed. Certainly if it can then the unspecified shift times to HH:MM calc can be flattened.

However, depending on what they are doing it may still be prudent to have the HH:MM derived value as an exact value (in other words this is where the rounding could happen). The reason to consider this it that subsequent calculations will be exact. Of course if the HH:MM really should have a smaller base unit (smaller than minute) then by all means make something like UTC datetime value. Note time value would still be an exact value.

One universal constant is that time and money are tricky business to get right (much like cryptographic algorithms).

Commenting anonymously (1 of ?) is not helpful if you then want to reference your previous anonymous recommendations ...

This is simonh (my account that I created yesterday has been deleted)! No matter.

I'll leave this thread alone now, but I would like to thank the people who went out of their way to offer assistance. Not only for me, but for anyone else landing here after a web search.

I think the main points to take away are:

1. Store start and end times as a datetime. 1.1 Saving as a time value only will not take into account shifts that start one day and end the next. My program does not have this issue, however, best practice / common sense suggests point 1.

2. Calculate with integers instead of floats. This also makes complete sense as rounding errors / issues are bypassed.

Cheerio folks!

good job. Make an account again and come back and help others. And don't mind the feedback, even if it may sound like tough love. Folks here are very enthusiastic to help esp. when it comes to currency, time and floating point calculations. Even the most mundane question can go on for 20-30 responses. It is all very enjoyable and I always educational

Make an account again and come back and help others.

(Disclaimer: this message is from the person who deleted that account (and he knows full well why it was deleted).)

@simonh: play nice and keep a civil tongue, and you're more than welcome to re-create your account. Our tolerance for the likes of your last post (which only moderators saw) is, however, absolutely zero. We aim to operate a "business casual" environment with "family-friendly" language (in the 1900's sense of the phrase, not my step-kids' sense of it :/).

I'd just like to say (whether or not you feel this is off topic and decide not to post it) thank you for moderating the forum in this manner. I'm sorry that you have to waste your time dealing with these matters.

We moderators are committed to helping keep this forum a place where people feel welcome to bring their genuine SQLite problems, or report difficulties or errors encountered with the SQLite library operation or its documentation, and obtain useful tips, references to the docs, and examples of SQL virtuosity. Occasionally, brief deviations from a thread's topic are taken to remind participants how their contributions will promote that objective. We strive to keep the forum a pleasant place to learn and share knowledge and expertise.

Generally, moderation is done without rigid enforcement of legalistic rules, with a light hand. Sometimes, posts are edited in moderation. More rarely, they are rejected. Even more rarely, registered participants have their "no moderation required" status removed or their registration is deleted.

There is no formal set of rules imposed upon posts. We do not want to encourage people to test the boundaries of acceptability, instead preferring to encourage participants to strive for the above-mentioned objectives. That said, abuse, profanity, thread-hijacking (or AI-generated rubbish) and commercial promotions are likely to result in rejection or edits.

That said, abuse, profanity, thread-hijacking ...

Just to expand upon one of Larry's points: any sort of derogatory name-calling falls into the category of "abuse," no matter how family-friendly (to use my prior description) it may be.

Better to use int cents

We use thousandths of a cent, because we have the space and it helps to highlight certain assumption errors.

However I think this is unnecessary in general.

Working in integers and in UTC and with fully specified datetimes helps avoid most of the most common pitfalls (yes this is just a long way of saying "I agree with Andy").

large fraction do not

I see what you did there 👍