I see the option to add days or hours, but what about if I need to add minutes? Can that be done?
Thanks
Bruce
Can I add Minutes to TimeStamp?
Re: Can I add Minutes to TimeStamp?
I would convert minutes to hours. I don't think the minutes function exists.
-
- Posts: 1463
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Can I add Minutes to TimeStamp?
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.
Bob
Re: Can I add Minutes to TimeStamp?
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 00PointsWell wrote: ↑Thu Jun 23, 2022 6:03 am The whole area of Time is inconsistently managed.
There's no WEEK_ADD or YEAR_ADD either.
Bob
Re: Can I add Minutes to TimeStamp?
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
Bruce
-
- Posts: 1463
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Can I add Minutes to TimeStamp?
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
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
-
- Posts: 7527
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Re: Can I add Minutes to TimeStamp?
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.
If you round to the closest value that is bigger than the period (0.2834) you will get what you want.
Aware IM Support Team
Re: Can I add Minutes to TimeStamp?
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
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
-
- Posts: 2417
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Can I add Minutes to TimeStamp?
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!!!
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!!!
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Re: Can I add Minutes to TimeStamp?
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.
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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL
Re: Can I add Minutes to TimeStamp?
If you set your number attribute to "decimal" you will have more control over the rounding aspect stored in the DB :But that does not always work... like in the second series which I am still trying to solve.
Irritating!!!
e.g. on the number setup, under Advanced, change SQL Type to: decimal (19,4)
-
- Posts: 2417
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Can I add Minutes to TimeStamp?
Thanx ACDC.
Will check that out.
Will check that out.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....