MySQL Dates and Times Pulled Into Access As Long Integer? 1

[kjv1611]

How can I convert the Long Integers given from a MySQL database to datetime values in Access?

Here are some examples of real values I'm getting back:

1233060995
1233060885
1233062676
1233060885

Each line a value for a different record.

Is it stored in a totally different format? I can't think of what it's called, but seems it started with a J. I've not needed to even think about that one for several years now..

Thanks for any suggestions/advice/examples/references

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Okay, I found an answer here:

http://able2know.org/topic/70939-1

It's a forum thread, and the specific bit of help is this:

Hello anyone who landed on this forum thread:

Unix Time Stamp records seconds since '1970-01-01 00:00:00'

Microsoft store a number representing the number of complete days since '1970-01-00 00:00:00' i.e. 1 = '1970-01-01 00:00:00'

So, Microsoft's date value for the beginning of the unix era '1970-01-01 00:00:00' is 25569

The number of seconds in 25569 days is:
25569*24*60*60
= 2209161600

The number of seconds in 1 day is:
24*60*60
= 86400
So in Access:

HumanDate: Format(([unixdate]+2209161600)/86400,"dd/mm/yy hh:nn:ss")

Only difference for me is I'd change the dd/mm to mm/dd, since that's what I'm primarily accustomed to.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Steve,

If that is true, then will return a date

dim d as date
d = ([unixdate]+2209161600)/86400

FORMAT converts a date to a STRING...

di8m s as string
s = Format(([unixdate]+2209161600)/86400,"dd/mm/yy hh:nn:ss")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Thanks, I'll take a look into that. The other worked for what I needed, but I'll see how that one does as well. It'll be later this evening or another evening, as it's something I'm piddling with at home. Thanks for the note.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Thanks, I finally just verified your statements, Skip. I don't know yet for sure which format I'll end up needing. I think I may use the text format initially just for viewing, and then I may take the conversion out of the picture altogether, b/c basically, I'm going to export all this data after I've regrouped it all into a CSV file, into a new MySQL database. It's going from an old copy of a website into a new copy of the same site (Drupal 6 to Drupal 7 - both using MySQL as the database).

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Eek! I just realized that this calculation is mostly[/] correct, but it is somehow adding 5 hours to the time of the calculation.

So if anyone has a suggestion on where to change it, that'd be great. I'll see if I can figure it out in the mean time. I guess I need to see how much an hour is in the calculation, and then reduce it by that times 5...

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

And oops... I left an i out of the tgml.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Okay, never mind on the last bit. I just used the DateAdd function in the access query to drop off 5 hours, and that corrected it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57