Date issue exporting postgres database to Access 2010

Postgres version 8.3
Access 2010

Connecting to the postgres database via their ODBC driver using Access 2010, I used the Access 2010 import wizard to pull over the patient demographics table.  The problem is that all the birthdates look like this in Access:

60411680000000000
63044006400000000

Obviously, this isn't going to work.  I need 11/21/2001 and so forth.

Am I doing something wrong?  Is there anyway to get the birthdate to come over the way I need it to?

Thank you all in advanced for your help.

JamesNT
JamesNTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
Looks like these dates are stored in Unix time format.
A number of that length must be held in Access as a text field, not a number.
So to convert to windows time format you can try...
dateadd("s", val(left(fieldname,9)), dateserial(1970,1,1))

Obviously you need to check a few values against the dates you expect to see.
0
Gustav BrockCIOCommented:
Could be something like this:
Public Function DateFromPostgres( _
  ByVal strMicroSeconds As String) _
  As Date
  
' Converts Postgres time string from ODBC to UTC date value.
  
  ' Postgres epoch (start time).
  Const cdatPostgresEpoch   As Date = #1/1/2000#
  ' Count of seconds of one day.
  Const clngSecondsPerDay   As Long = 24& * 60& * 60&
  
  Dim dblDays               As Double
  Dim dblSeconds            As Double
  Dim dblDaysSeconds        As Double
  Dim lngSeconds            As Long
  Dim datTime               As Date
  
  dblSeconds = Val(Left(strMicroSeconds, 8))
  ' Limit intervals for DateAdd to Long to be acceptable.
  dblDays = Int(dblSeconds / clngSecondsPerDay)
  dblDaysSeconds = dblDays * clngSecondsPerDay
  lngSeconds = dblSeconds - dblDaysSeconds
  
  datTime = DateAdd("d", dblDays, cdatPostgresEpoch)
  datTime = DateAdd("s", lngSeconds, datTime)
  
  DateFromPostgres = datTime

End Function

Open in new window


but values doesn't match your example.
Could you provide some samples please?

/gustav
0
JamesNTAuthor Commented:
peter57r,

Your solution is close, but all the years are coming out as 1989.  With a patient table that has over 10,000 rows, I doubt everyone was born in the same year.  :)

JamesNT
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

JamesNTAuthor Commented:
gustav,

Your solution spits out all dates as 1/1/2000.  I gave some actual date samples above.  Are you looking for something more specific?

JamesNT
0
peter57rCommented:
What do you think are the correct convertions for the values you posted?
0
JamesNTAuthor Commented:
Let me get a few side-by-side examples.  The software that uses PostGres shows the birthdate in its GUI.  Unfortunately, it will be Monday or Tuesday before I can get to the computer that has the information we need.  Until then, thank you for your patience and have a safe holiday weekend (assuming you celebrate what the United States calls Easter).

JamesNT
0
Gustav BrockCIOCommented:
Your two examples return:

2001-12-30 16:13:26
2001-11-30 05:01:20

which probably are wrong as a time part most likely is absent.

/gustav
0
JamesNTAuthor Commented:
gustav,

Interesting.  What really confuses me is that all the other dates from the export turned out fine (e.g. date of service, date of entry, etc.).

JamesNT
0
earth man2Commented:
select cast( 'epoch' as timestamp ) + (63044006400000000/100) * interval '1 microsecond';
= "1989-12-23 18:14:24"


Public Function DateFromPostgres( _
  ByVal strMicroSeconds As String) _
  As Date
 
' Converts Postgres time string from ODBC to UTC date value.
 
  ' Postgres epoch (start time).
  Const cdatPostgresEpoch   As Date = #1/1/1970#
  ' Count of seconds of one day.
  Const clngSecondsPerDay   As Long = 24& * 60& * 60&
 
  Dim dblDays               As Double
  Dim dblSeconds            As Double
  Dim dblDaysSeconds        As Double
  Dim lngSeconds            As Long
  Dim datTime               As Date
 
  dblSeconds = Val(strMicroSeconds)/(100*1000000)
  ' Limit intervals for DateAdd to Long to be acceptable.
  dblDays = Int(dblSeconds / clngSecondsPerDay)
  dblDaysSeconds = dblDays * clngSecondsPerDay
  lngSeconds = dblSeconds - dblDaysSeconds
 
  datTime = DateAdd("d", dblDays, cdatPostgresEpoch)
  datTime = DateAdd("s", lngSeconds, datTime)
 
  DateFromPostgres = datTime

End Function
0
JamesNTAuthor Commented:
Here is what we have so far:

PostgresBirthDate                              peter                         gustav                         earthman
623540160000000000          10/4/1989 9:36:00 PM        8/4/2197                      8/5/2167
609075072000000000           4/20/1989  11:31:12AM    1/2/2193 7:12:00 PM   1/3/2163 7:12PM

I have done some reading on the whole "Unix Time Format" thing.  I find it. . . not as cool as the way Windows handles time, but I digress.  Regardless, I see the math you guys are trying to perform.

With the results such as they are, I think we should consider the possibility that either:

A.  Someone is using their own screwed up date format in the software in question that uses Postgres as its database backend.

B.  The Postgres ODBC driver is doing something wrong in translation.

I state these in observation that other date fields came over just fine (e.g. 1/1/2012) and need no such translation at all.  Why the birthdate is the only one in this format is beyond me.

I'll try to have more information on Tuesday once I am able to access the computer with the postgres database on it again.

Thank you all for your patience.  Have a safe weekend.

JamesNT
0
Gustav BrockCIOCommented:
It is probably very simple. Numbers like 623540160000000000 cannot be handled natively by VBA, thus the ODBC driver converts the value to a string and then you are left with what to do next.

The same thing happens for the data type DateTime2 of SQL Server. VBA can only handle down to milliseconds, thus the value is converted to a string to hold the time part below milliseconds.

/gustav
0
JamesNTAuthor Commented:
OK.  Here is some new information.  

PatientID        AccessBirthDate                         SoftwareBirthdate
505345           624008448000000000                5/29/1978
345711           615675168000000000                 1/1/1952
239844           623172960000000000                  10/5/1975

The "AccessBirthdate" is what I am getting in Access after using the import wizard to pull the patients table over to an Access database.  The "SoftwareBirthdate" is what is being displayed as the patient's birthdate in the application that uses Postgres as it's database.

NOTE:  I have noticed that when using the Access import wizard, the Birthdate comes over as a NUMBER, not as a String.  I do have to change it to a string for the above functions to work.

NOTE:  The application using Postgres as its database is known as Soapware.

Question:  Do we think these values may be being truncated during transport across the ODBC driver?

JamesNT
0
Gustav BrockCIOCommented:
Here is how:
Public Function DateFromSoapware( _
  ByVal strTime As String) _
  As Date
  
' Converts Soapware time Value to date Value.
'
' 2012-04-10. Cactus Data ApS. CPH.
  
  ' Soapware offset to fit data type Date.
  Const cdblBaseSeconds     As Double = 59926435200#
  
  ' Maximum time bias in seconds, 12 hours.
  Const clngSecondsBiasMax  As Long = 12& * 60&
  ' Count of seconds of one day.
  Const clngSecondsPerDay   As Long = 24& * 60& * 60&
  
  Dim datTime               As Date
  
  datTime = CDate((Val(Left(strTime, 11)) - cdblBaseSeconds) / clngSecondsPerDay)
  
  DateFromSoapware = datTime

End Function

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JamesNTAuthor Commented:
All dates came out as 12:00:00AM.

This is a most challenging problem!

JamesNT
0
Gustav BrockCIOCommented:
Not if you feed the values as strings and data are as your examples.

/gustav
0
JamesNTAuthor Commented:
I tried creating a separate table and adding two of the dates there and still got 12:00:00AM as the only answer.  

I also tried using TRIM() to make certain spaces were not the problem.  Still shows at 12:00:00AM.

You seem to know a bit about soapware.  Have you seen these issues before in this software?

JamesNT
0
Gustav BrockCIOCommented:
I have no knowledge about Soapware other than you mentioned it here.

If I run the three examples you provided, I receive the expected results:

datDate = DateFromSoapware("624008448000000000")
=> 1978-05-29

The values seems to be tenths of microseconds from a virtual year zero. This VB(A) can't handle, thus the initial subtraction of cdblBaseSeconds seconds.

The values could also be handled as Decimal but it would be unlikely that this data type was returned by the ODBC driver.

/gustav
0
JamesNTAuthor Commented:
Let me go through the code again to make sure I made no mistakes in entering it and all that.  I recommend doing the same on your end - to make sure what was copy and pasted to EE's site in your post above matches what you are using.

If that does not solve this problem, then might we assume this is a VBA issue in Access 2010?  I assume you used the same version of Access as I.

I'll report back as soon as I can.  Thank you again for your patience.

JamesNT
0
Gustav BrockCIOCommented:
Actually it was Access 97, but I can't imagine that would make any difference. The code is extremely basic.

/gustav
0
earth man2Commented:
select ('29-MAY-1978'::timestamp - '01-JAN-1952'::timestamp)*24*60*60, 624008448000000000 - 615675168000000000,615675168000/(365*24*60*60);

gives seconds between 01-JAN-1952 and 29-MAY-1978 as  833328000
624008448000000000 - 615675168000000000 = 8333280000000000
therefore the units are seconds * 10**(-7)

615675168000/(365*24*60*60) is 19522 ie  (19522 - 1952) BC

Dates that far back in time are nonsensical to measure in units of 10E-7 seconds!

Best to settle on gregorian calendar as the datum.
15-OCT-1582 is 604023955200000000

select  604023955200000000::bigint + extract( days from ('01-JAN-1952'::timestamp - '15-OCT-1582'::timestamp))::bigint *24 * 60 * 60 * 1000000;
0
JamesNTAuthor Commented:
Syntax missing operator in

select  604023955200000000::bigint + extract( days from ('01-JAN-1952'::timestamp - '15-OCT-1582'::timestamp))::bigint *24 * 60 * 60 * 1000000;

I'll try to fix this myself but do remember I am using Access 2010.  Let me know if you see what is wrong.

JamesNT
0
Gustav BrockCIOCommented:
It has to be run as a pass-through query directly in Postgres because the syntax is not JET SQL, and BigInt is not supported in VBA.

Also, the values in the SQL above:

604023955200000000

differs by in length by one from the example you posted:

60411680000000000

/gustav
0
JamesNTAuthor Commented:
I have to finish up a database conversion for another client today and will be out-of-town tomorrow installing a VPN router (installing the router should be easy, it's the 2 hour drive just to get there that will be time consuming).  

Thank you all for your phenomenal patience.  Clearly, I have never used postgres before (and hope I never do again at this point).  I'll get back to this as soon as I can.

JamesNT
0
Gustav BrockCIOCommented:
No problem.
And don't blame Postgres. It is well reputated but, of course, it's own. BigInt is widely used.

/gustav
0
JamesNTAuthor Commented:
Ladies and Gentlemen,

I have not forgotten this thread at all and this is still important.  Please understand that I am currently under a bit of a heavy load right now and am being stretched in a few different directions at once.

Once I am able to get my head above water and take a decent breath, I will be returning to this thread to retry all currently proposed answers to double-check that I am not doing anything wrong and then will submit feedback.

I truly appreciate everyone's patience and understanding.  I am certain that as developers/IT Pros yourselves, you too have had days/weeks/months/lifetimes like the one I am in now.

JamesNT
0
Gustav BrockCIOCommented:
No problem.

/gustav
0
JamesNTAuthor Commented:
I am in the process of going through all proposed solutions again to make sure I followed instructions correctly.

JamesNT
0
JamesNTAuthor Commented:
Thank you for your assistance.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.