Solved

Date issue exporting postgres database to Access 2010

Posted on 2012-04-05
29
442 Views
Last Modified: 2012-05-21
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
0
Comment
Question by:JamesNT
  • 14
  • 11
  • 2
  • +1
29 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37815259
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37815273
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
 

Author Comment

by:JamesNT
ID: 37817355
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
 

Author Comment

by:JamesNT
ID: 37817381
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
 
LVL 77

Expert Comment

by:peter57r
ID: 37817414
What do you think are the correct convertions for the values you posted?
0
 

Author Comment

by:JamesNT
ID: 37817461
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37817558
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
 

Author Comment

by:JamesNT
ID: 37817716
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
 
LVL 22

Expert Comment

by:earth man2
ID: 37820301
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
 

Author Comment

by:JamesNT
ID: 37820496
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37820668
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
 

Author Comment

by:JamesNT
ID: 37828112
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 37828522
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
 

Author Comment

by:JamesNT
ID: 37828654
All dates came out as 12:00:00AM.

This is a most challenging problem!

JamesNT
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37828681
Not if you feed the values as strings and data are as your examples.

/gustav
0
 

Author Comment

by:JamesNT
ID: 37828904
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37831206
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
 

Author Comment

by:JamesNT
ID: 37832602
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37832628
Actually it was Access 97, but I can't imagine that would make any difference. The code is extremely basic.

/gustav
0
 
LVL 22

Expert Comment

by:earth man2
ID: 37834143
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
 

Author Comment

by:JamesNT
ID: 37834377
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37836226
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
 

Author Comment

by:JamesNT
ID: 37837191
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37837216
No problem.
And don't blame Postgres. It is well reputated but, of course, it's own. BigInt is widely used.

/gustav
0
 

Author Comment

by:JamesNT
ID: 37898560
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37901022
No problem.

/gustav
0
 

Author Comment

by:JamesNT
ID: 37958537
I am in the process of going through all proposed solutions again to make sure I followed instructions correctly.

JamesNT
0
 

Author Closing Comment

by:JamesNT
ID: 37993258
Thank you for your assistance.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37994306
You are welcome!

/gustav
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now