Question about TIME field

[hanglam]

My company bought this application which uses Pervasive/Btrieve SQL as the backend. In the database one of the fields is a TIME field. When I examined the tables, some of the TIME fields are saved as 7-digits numbers, i.e. "3352919" .
Does anyone knows how to convert this number into a readable time fields, i.e "12:02 PM" ? or what kind of TIME formatting this database is using to store the time values?

Thanks,
Scott

 

[hanglam]

Never mind the question.
I just got the answer from the vendor.

Thanks,

 

[alvechurchdata]

I just got the answer from the vendor.

We're all holding our breath out here. Tell us more.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[hanglam]

Sorry, I didn't think anybody was interested, but here it goes .

7 digit number = Hours * 360000 + Minutes * 6000 + Seconds * 100.

Example:

Time: 09:18:50 (HH:MM:SS)
Number = 09 * 360000 + 18 * 6000 + 50 *100 = 3353000

Data:
Number = 7 digit long number = 3352919
Hour = Hours (HH from HH:MM:SS)
Min = Minutes (MM from HH:MM:SS
Sec = Seconds (SS from HH:MM:SS)

Steps to convert this number into actual time format.

1. Hour = Integer (Number / 360000)

Hour = Integer (3352919 / 360000) = 09

2. Number = Number - Hour * 360000

Number = 3352919 - 09 * 360000 = 112919

3. Min = Integer (Number / 6000)

Min = Integer (112919 / 6000) = 18

4. Number = Number - Min * 6000

Number = 112919 - 18 * 6000 = 4919

5. Sec = Integer (Number / 100)

Sec = Integer (4919/ 100) = 49

Output:
Hour:Min:Sec = 09:18:49

 

[alvechurchdata]

Sorry, I didn't think anybody was interested

Thanks for getting back. I've no possible reason for needing to know the answer apart from curiousity.

These time formats are usually "seconds since ...." but a bit of arithmetic convinced me it wasn't "seconds since midnight" or "seconds since 01/01/1980".

Turns out that it's "hundredths of a second since midnight". The only question now is "why?" but I guess we'll never know.

Geoff Franklin

http://www.alvechurchdata.co.uk