Can I add Minutes to TimeStamp?

BLOMASKY

I see the option to add days or hours, but what about if I need to add minutes? Can that be done?

Thanks
Bruce

Rennur

I would convert minutes to hours. I don't think the minutes function exists.

PointsWell

The whole area of Time is inconsistently managed.

There's no WEEK_ADD or YEAR_ADD either.

BobK

Rennur wrote
I would convert minutes to hours. I don't think the minutes function exists.

To convert minutes to hours use fractions.

example:
TIME_ADD('2022-06-23 08:30:00', 0.50)
returns '2022-06-23 09:00:00'

Note: I think it was fixed several builds back, but there was a time when subtracting fractions (-0.50) did not work.

BobK

PointsWell wrote
The whole area of Time is inconsistently managed.

There's no WEEK_ADD or YEAR_ADD either.

There has been a few times I wish there was a START_OF_DAY (similar to START_OF_WEEK) that returned a timestamp with HOURS, MINUTES and SECONDS all set to 00

BLOMASKY

The problem with Fraction hours is that if I want to add 17 minutes, i get a repeating fraction. Wonder how hard it would be for Vlad to add MinuteAdd???

Bruce

PointsWell

It would be relatively trivial in that it is functionality contained within commons-lang3 jar and not something being written from scratch.

There is a lot of functionality that exists within the shipped JARs that has not been exposed for example

commons-validator

EAN Validator - checks a barcode is compliant (+additional specialist applications such as ISBN)
ABA Validator - checks a routing number is compliant
LUHN Validator - checks a credit card number is compliant

commons-codec is another one that contains really useful functions like Soundex (and its specialisations and derivatives) which would make searches more dynamic as it would pick up similarities rather than straight matches.

aware_support

17 min of an hour is 17/60 = 0,2833333333....

If you round to the closest value that is bigger than the period (0.2834) you will get what you want.

BLOMASKY

however MINUTES_ADD(bo.startTime, bo.minToAdd) is much easier to read and understand than
TIME_ADD(bo.startTime, round(bo.minToAdd / 60 + 0.0005,4))

and since I have to add 0.0005 to make sure it rounds properly, will this mess up 59 minutes?

Bruce

customaware

I have had no end of drama with the ROUND Function.

I am convinced it does not always work correctly.

In my mind if I want ROUND(x,1) then the result should ALWAYS ... and I mean ALWAYS have 1 Decimal place....

Not like this nonsense...

[3.1,0.4,0.30000000000000004,0.8,-0.6000000000000001,0.7000000000000001,-0.1,0.4,-0.1,0.1,-3.4000000000000004,-4.1000000000000005,-2.2,-1.5,-4.1000000000000005,0.2,-1.4000000000000001,-0.6000000000000001,-2.8000000000000003,0.0,0.5,0.9,0.30000000000000004,-1.8,0.1,0.1]

Regardless of all the nonsense about roundin up, down and inside out.... I think the only way to get this right is to add an Option Truncate Flag to the ROUND function.....

So....
ROUND(X,1) might return 4.1000000000000005
But set the option Truncate Flag as the 3rd Parameter....
ROUND(X,1,1) will return 4.1

This would fix it once and for all.

I cannot count the hours and hours I have spent manipulating values needlessly trying to get the ROUND to work.... And have not always been successful... like the last 7 hours today 🙁

I have 2 Series in a Chart.......
Resolved the first by ROUND to zero after multiplying by 10 and then dividing by 10

ROUND ( SUM Trip.DeltaMinutesArrive WHERE (Trip.TripStatus='ARRIVED' AND Trip.YearWeek = 1 AND Trip.Year =YEAR(CURRENT_DATE)) / 60 *10 , 0) / 10

But that does not always work... like in the second series which I am still trying to solve.

Irritating!!!

Jaymer

That issue with the long precision is just a long standing IT reality when you store numbers as real data types.
What real issue does that cause you mark?
Because how it’s stored internally is ultimately binary right, so the issue is how it’s displayed.
So perhaps more intelligence needs to be added on output of these long precision numbers. Of course it already does this when your format is #.#, it’s already rounding for you on output.
And that long number with 10+ decimal points gets rounded just fine. It can have all sorts of gibberish out there but it’s going to get rounded to one decimal point already.

ACDC

But that does not always work... like in the second series which I am still trying to solve.

Irritating!!!

If you set your number attribute to "decimal" you will have more control over the rounding aspect stored in the DB :

e.g. on the number setup, under Advanced, change SQL Type to: decimal (19,4)

customaware

Thanx ACDC.
Will check that out.