How to convert a long number to "hh:mm:ss" ?

[dschomburg]

I have a long int. which represents "total seconds".
I would like to change this (in a select query) to "hh:mm:ss" format.

I have seen a few examples but nothing really for the expression builder.

Any help would help a heap.

Thanks,
Dave

 

[chaufschild]

It might not be the most simple way to do it but my first thought is that you could set up the query to make each part of the date separately...like the following (s being your field with the seconds):

Seconds : (s/60 - int(s/60)) * 60
Minutes : (int(s/60)/60 - int(s/60/60)) * 60
Hours : (int(s/60/60)/60 - int(s/60/60/60)) * 60

Then you could combine them into your needed field. Again...this is probably not the easiest way but it is what came to mind first...

Chris

 

[dschomburg]

Thank you so much for the help. I can certainly throw these together in one long expression. No problem.
However I think there is something wrong with the Hours part.
I'm starting with = 1452295 seconds
which should be 403 hours, but using your formula it comes out to 43 hours.

 

[chaufschild]

Sorry about that -- after reviewing it it looks like the hours equation should be this:

int( int (s/60) / 60)

That should work better...

 

[dschomburg]

Groovy!
Thanks a heap. I have been messing with this for hours, and my brain is melting. I can't believe MS doesn't have a Time converstion function built in.

Thanks again,
Dave

 

[raymondo]

As a not very different alternative you could use the mod operator.
If s is your total seconds then build it up in stages in your query as follows

modm:mod(s,3600)
hs-modm)3600
mods:mod(modm,60)
mmodm-mods)/60
hhmmss:h & m & mods each converted to strings and formatted with zeros etc Raymondo
raymondo@rossar.net

 

[raymondo]

Small correction
col1 - modm:mod(s,3600)
col2 - hs-modm)/3600 <------
col3 - mods:mod(modm,60)
col4 - mmodm-mods)/60 Hope this helps,
Raymondo
raymondo@rossar.net

 

[dschomburg]

Thanks all,
The trick is I really needed to get this all in one field, due to the query being the object exported.
So I ended up with this. All in 1 expression

Avg AVAILABLE per Skill: Int((Int([TEMPAVAIL]/60)/60)) & &quot;:&quot; & Int((Int([TEMPAVAIL]/60)/60-Int([TEMPAVAIL]/60/60))*60) & &quot;:&quot; & Int(([TEMPAVAIL]/60-Int([TEMPAVAIL]/60))*60)

 

[MichaelRed]

I tried your soloution (1452295) ==> 403:23:54, but it doesnt 'feel' right, so I did it another way and got 403:24:55.

Since these are blatently not equal, I did a reverse calc&quot;

? (403. * 3600) + (24 * 60) + 55.
1452295

which is my way of backing into the aparent fact that 403 Hr, 24 Min and 55 Secs is the same as the 1452295 seconds.

Since you apprar to be a 'purist' re SQL (w/o procedures), I do not think you will want to use this, however it may give you some help in the debug / development of hte SQL soloution.

Public Function basLngSec2Time(SecsIn As Long) As String

    'Michael Red    7/15/02
    'Convert Seconds (as Long) to a "Time" string ~ h:m:s
    
    'basLngSec2Time(1452295)
    '403:24:55

    Dim MySecs As Double        'Number of Seconds in the working unit
    Dim MyHrs As Long           'Number of Hours
    Dim MyMin As String        'Number of Minutes
    Dim MySec As Integer        'Number of Seconds
    Dim WrkSecs As Long         'Time to work with - initally the # Seconds in the input

    Const SecsPerDay As Long = (60# * 60 * 24)   '~86400

    'Initalize the WrkSecs
    WrkSecs = SecsIn

    'So get the asconds into STANDARD date-time format to Start
    MySecs = WrkSecs / SecsPerDay


    'Get the total Hours in the input
    MyHrs = Int(SecsIn * (24 / SecsPerDay))

    MySecs = SecsIn - (MyHrs * (SecsPerDay / 24))

    MyMin = Format((MySecs / SecsPerDay), "h:mm:ss")
    basLngSec2Time = MyHrs & right(MyMin, 6)

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[dschomburg]

Here's the easiest way:

Expr1: Format([YourTable]![YourField]/(24*60*60),&quot;h:nn:ss&quot