Conversion of UTC Value 1

[brum64]

Does anyone know how to convert a UTC value into a workable date field?

 

[MalcolmW]

What is the format of the UTC field? Does it have a date value, or is it just the time? Do you want to show the date, time, or datetime value of this field? Malcolm
wynden@telus.net

 

[brum64]

The field is a number field of length 38...I'm not sure how it is created..it is done by an in the box application..to which I have no access to the source code.
I would like to determine the date and time from this field
Here is an example of what the value of the field is...
938747887

Not sure if any of this helps...
I was under the impression that the UTC value is some number of seconds?? from a date in 1970 or something?

 

[MalcolmW]

I thought it was Jan 1, 1972.
UTC has occasional leap seconds as well, so there is not a fixed number of seconds in day. You can ignore these and get a time that is very close to being right - i think there are only about 15 leap sec since time zero Malcolm
wynden@telus.net

 

[brum64]

OK...I've found out that the field is the current time in seconds and I would like to convert that into a workable date and time...is there an Oracle Function or an easy way to do this?

 

[kenhamady]

The value you gave represents about 30 years worth of seconds. So you need to know the starting point to do the calculation.

Lets say the starting date was 1/1/1970

To get the datetime value you could use:
DateTime(1970,1,1,0,0,0) + ({field}/86400)


(86400 is the number of seconds in a day)


Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

If it is truly UTC, then Ken's formula should do it (it was 1972 when the switch from GMT to UTC happened though) - you would be better off to do the conversion to a date in your database, as you can be assured that an Oracle back end is going to be several orders of magnitude faster than doing the conversion through SCR on the client.
About every second or third year has a leap second though, so unless you establish a table for leap seconds you will have a cumulative error of a few seconds.

There is an Oracle forum on Tek-Tips that would be able to give you some tips on the best conversion.

Malcolm
wynden@telus.net