Rounding the current time to next hour PLEASE HELP

[THEJOESTER]

Hi, this database that I am making is for a train station that has runs on the hour. What I want is the code to make the current time (Time()) to be rounded to the next hour. Example: the time is 9:04, I need it to be rounded to 10:00. I need this so I can use it in queries so I dont have to keep going into the queries and changing the time value. Please Help. Much Appreciated.

 

[NasibKalsi]

t_TimeTrans = '01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,00'

t_time = time()

t_CurrentHour = val(left(t_time,2))
t_NextHour = subst(t_TimeTrans,3*t_CurrentHour+1,2)
? t_NextHour

*
* or you can use the following code
*
t_time = time()

t_NextHour = mod(val(left(t_time,2))+1,24)
t_NextHour = right('00'+ltrim(str(t_NextHour,2)),2)

? t_NextHour

try it


Nasib

 

[Imaginecorp]

Hello Nasib;
It is returning a zero

 

[Imaginecorp]

Here is a different approach, if using VFP8/9 else change the Icase() to IIF's

? Icase((Val(Strextract(Time(),"",":",1)) >= 24 And Val(Strextract(Time(),":",":")) > 0),"01:00",;
(Val(Strextract(Time(),"",":",1)) >= 24 And Val(Strextract(Time(),":",":")) = 0),Strextract(Time(),"",":")+":00",;
TRANSFORM(Val(Strextract(Time(),"",":"))+1)+":00")

Have not fully tested it in every scenerio, but can also be used in a SQL-Select

 

[Imaginecorp]

Nasib: What do you think? Should work (I hope...)
Will have to be fine tuned..

 

[NasibKalsi]

Imaginecorp: I did not check your code yet. Instead of putting a ; (semicolon), make a new statement. It is easier to read and debug.

But I am wondering why TheJoester is saying it returns 00. Because it is 23:00 hours now ? And the next hour is 00:00 not 01:00 or 24:00.

TheJoster: Please try changing your time to some other values and run the program for each. Let us know.
I forget to add :00. Add the next line at the end of the my code.

t_NextHour = t_NextHour + ':00:00'

IT MUST WORK.


With the first solution you can change to any hour. For example when it is
00 -> 01
01 -> 02
and so on. And you can put
00 -> 02
01 -> 03
and so on.


Nasib

PS:I just learned how to highlight Code, quote, etc.

 

[baltman]

dDate = DATE()
nSeconds = SECONDS()
?CTOT(DTOC(dDate)+" 0:00.00")+(CEILING(nSeconds/3600)*3600)

Brian

 

[baltman]

Slightly better...

?DTOT(DATE())+CEILING(SECONDS()/3600)*3600

 

[NasibKalsi]

Sorry THEJOESTER: I misread the posting of Imaginecorp as coming from you.

Imaginecorp: Yes, it "works". I think, after 23:00 the next hour should be 00:00 not 24:00. The clock runs from 00:00 - 23:59 and not 01:00 to 24:59. However, in conversations we use 12:00 o'clock midnight not 24:00 o'clock. I should do some research to find out exactly, what is correct.

Regarding your code: I just tested it. It works but as you said, needs fine tuning. It return 24:00 at 23:00. It also return 1:00 not 01:00. SO if you can fix those two items, it is sellable. Can you revise and post ?

Nasib

 

[NasibKalsi]

Baltman:
1. The way you have it, prints the complete date and time.
2. Also when the time is exactly at 10:00:00, 11:00:00, etc. the program will output wrong answer.

Needs refining.


Nasib

 

[Olaf Doschke]

Hi Joester,

simply use

? ChrTran(Str((Val(Time())+1)%24,2)+":00:00"," ","0")

If you want 0:00:00, 1:00:00, ... instead of 00:00:00, 01:00:00, ... remove the final ChrTran() operation.

Bye, Olaf.

 

[Imaginecorp]

Nasib: you are right. Like I said, never really tested it. It was more to give an Idea.

The best solution, IMO, is Olaf's...Short and effective.

 

[baltman]

I wasn't trying to give an exact solution, but to demonstrate the approach of rounding up/rounding up time.

 

[THEJOESTER]

Sorry guys, but I am a stupid beginner. Where would I put this code? I don't know where I would put this code. I already tried some places but didn't work. Thanks.

 

[baltman]

nNextHour=1+MOD(HOUR(DTOT(DATE())+CEILING(SECONDS()/3600)*3600),24)

Select MySQL Where HOUR(MyDateTimeField)=nNextHour

 

[THEJOESTER]

Please explain to me EXACTLY where I would put it. Thanks

 

[baltman]

Where are the queries you are running?

 

[THEJOESTER]

SELECT Sum([Seniors]+[Adults]+[Children]+[Family Special]+[Family Extra]+[Comps]+[Group Special]) AS Expr1
FROM Customers
GROUP BY Customers.[Train Date], Customers.[Train Time], Customers.Reservations
HAVING (((Customers.[Train Date])=Date()) AND ((Customers.[Train Time])=Time()) AND ((Customers.Reservations)=Yes));

 

[baltman]

[red]nNextHour=1+MOD(HOUR(DTOT(DATE())+CEILING(SECONDS()/3600)*3600),24)[/red]


SELECT Sum([Seniors]+[Adults]+[Children]+[Family Special]+[Family Extra]+[Comps]+[Group Special]) AS Expr1
FROM Customers
GROUP BY Customers.[Train Date], Customers.[Train Time], Customers.Reservations
HAVING (((Customers.[Train Date])=Date()) AND ([red]hour([/red](Customers.[Train Time])[red])[/red]=[red]nNextHour[/red])) AND ((Customers.Reservations)=Yes));

 

[THEJOESTER]

Hey Baltman, I copied and pasted into the MySQL view of the query and this error comes up "Expected DELETE, INSERT, PROCEDURE, SELECT, OR UPDATE and then it points to nNextHour in this statement: nNextHour=1+MOD(HOUR(DTOT(DATE())+CEILING(SECONDS()/3600)*3600),24)