JulianTimeStamp Conversion 3

[SteveJR]

All,

Does anyone know how to convert a 64 bit JulianTimeStamp into the format DD-MM-YYYY, HH:MM:SS.NNNNNN as I am having a few problems!

64-bit JulianTimeStamps are in microseconds since Jan 1st 4713 BC 00:00.

An example timestamp is : 211864761000643661
This converts to : 16-AUG-2001, 22:30:00.643661

Any help would be much appreciated.

Thanks,

Steve

 

[CaKiwi]

Steve,
What assumptions are made about leap years? Our current system of a leap every 4 years except for years divisible by 100 but not 400 began in the 18th century. Do we need to take into account the fact that in England and her colonies September 1752 had 11 days removed?

CaKiwi

 

[CaKiwi]

Also can we assume that the years go:
... 2BC 1BC 1AD 2AD ...

And how will the 64 bit number be stored when you get it?
Two unsigned longs, a string or what?

CaKiwi

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmm,

and

Hmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm,

again.

You are dealing with some large number here. I expect you need to convert much of the math to DOUBLE type and still do it in increments.

See the folloowing:

? datediff("d", DateSerial(0, 1, 1), DateSerial(4713, 1, 1))
990903

? DateDiff("d", DateSerial(1899, 12, 30), DateSerial(0, 1, 1))
36526

? CDbl((990903 + 36526) * 86400 * 1.e6)

? CDbl((990903 + 36526))
1027429

which you have over 1 million days from the "Start" just to get to Ms. date "zero"

With 8.64e10 micro second per day, the math is a bit tricky. so you need to do some incremental subtractions to get the numbers back into mere 64 bit capability

I would suggest taking the above items of info, study HOW the number of days is done for the intervals (they are NOT exactly Correct at the moment), derive the CORRECT offsets (in Days), then figure out how to Incrementally subtract "lumps" of time until you get the value down to "Ms time". After that, it should be just converting the remainder to the Double which Ms. Dates expect.

MichaelRed
mred@att.net

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

 

[strongm]

Option Explicit



Private Sub Command1_Click()
Dim result As Date
result = GregorianFromJulian(2.11864761000644E+17)
MsgBox Format(result, "dd-mmm-yyyy hh:mm:ss&quot

End Sub

Public Function GregorianFromJulian(lJulian As Double) As Date
Dim LongDate As Double
Dim lSecs As Long

LongDate = ((((lJulian / 1000000) / 60) / 60)) / 24
lSecs = (LongDate - Int(LongDate)) * 24 * 60 * 60
GregorianFromJulian = DateAdd("s", lSecs, GetGregorian(Int(LongDate)))

End Function

' Algorithm derived from letter to Communications of the ACM by
' Henry F. Fliegel and Thomas C. Van Flandern entitled "A Machine Algorithm for Processing Calendar Dates"
' It appears in CACM, volume 11, number 10, October 1968, p. 657
' Note this ONLy gives accurate dates within the Gregorian calendar period
Private Function GetGregorian(LongDate As Long) As Date
Dim dDay As Long, dMonth As Long, dYear As Long, l As Long, n As Long, i As Long, j As Long


l = LongDate + 68569
n = Int((4 * l) / 146097)
l = l - Int((146097 * n + 3) / 4)
i = Int((4000 * Int((l + 1)) / 1461001))
l = l - Int((1461 * i) / 4) + 31
j = Int((80 * l) / 2447)
dDay = l - Int((2447 * j) / 80)
l = Int(j / 11)
dMonth = j + 2 - (12 * l)
dYear = 100 * (n - 49) + i + l



GetGregorian = CDate(Str(dDay) + "/" + Str(dMonth) + "/" + Str(dYear)) ' Assuming UK date format
End Function

 

[SteveJR]

Strongm,

Thankyou. The code works like a dream.

I can't believe I never used this site before. The expertise on it is amazing.

Thankyou again.

Steve

 

[SteveJT]

StrongM,

Have noticed than the code you supplied returns the time/date 12 hours in the past. I have added the code
lSecs = lSecs + 43200 just before the dateadd command.

This seems to work but I wanted to get your opinion.

Thanks,

Steve

 

[strongm]

That should be fine. I have to admit that I didn't test the code thoroughly; I just translated the orginal algorithm into VB

 

[SteveJR]

StrongM,

Thanks. Can you help me without another problem. I have posted the below thread and would be grateful for any assistence you can provide.

thread222-124370

Thanks,

Steve

 

[scorpio66]

Hmm Steve,

Given that you found Mikes post so helpful, it might be worthwhile 'letting him know ...'

He might feel more inclined to give you further assistance if his help is appreciated...

Chaz

 

[SteveJR]

Chaz,

I did actually thank Mike a couple of responses ok. My last thankyou was to confirm something I added to the code he provided.

I am extremly grateful for all the help and code that has been supplied by Mike not just in this thread but in the others that I have raised aswell.

Steve

 

[scorpio66]

Sorry Steve, I wasn't implying that you were ungrateful - in fact its quite obvious that you are grateful.

I was making a point that a star is a reward for good advice, and as a system it really does work. People feel far more disposed towards helping you if you reward their advice. Where else can you get this level of support for the cost of a mouseclick?

Believe me, Mike earns plenty of stars because his advice is excellent (as are others - sorry, this isn't an 'I love Mike' message) which is why he is top of the experts list.

Sorry if there was a misunderstanding.

Chaz

 

[SteveJR]

Sorry Chaz,

I didn't realise you meant to reward Mikes comments/answers with the star system. I am new to the Tek-Tips site so I am still learning how to use all the smileys/reward symbols. I agree with you about Mikes expertise, it has been 5* so far!

Cheers,

Steve