st: Re: Cannot get date fields to import from PostgreSQL via ODBC

["Kevin Crow - StataCorp" <kcrow@stata.com>]

Dave,

You might want to try the -allstring- option of the -odbc load- command. 
For example,



. clear



. odbc list



Data Source Name                   Driver

-------------------------------------------------------------------------------

Bug                                PostgreSQL

Cert                               PostgreSQL

ODBCTest                           PostgreSQL

Tserve                             MySQL ODBC 2.50 Driver DSN

-------------------------------------------------------------------------------



. odbc exec("select * from dates"), dsn(odbctest)



   1. 
+-----------------------------------------------------------------------+

      |a | 2005-09-23 
|

      |b | 10:20:30 
|

      |c | 2004-01-01 19:27:03 
|

      +-----------------------------------------------------------------------+







. odbc load, exec("select * from dates") dsn(odbctest)



. list



    +----------------------------------+

    |         a          b           c |

    |----------------------------------|

 1. | 23sep2005   10:20:30   01jan2004 |

    +----------------------------------+



. clear



. odbc load, exec("select * from dates") dsn(odbctest) allstring



. list



    +---------------------------------------------+

    |          a          b                     c |

    |---------------------------------------------|

 1. | 2005-09-23   10:20:30   2004-01-01 19:27:03 |

    +---------------------------------------------+



Also, I believe there was a bug fix to the -odbc- command so make sure Stata 
is updated.

In the above example I was using postgresql 8.0.3.



Kevin

StataCorp


----- Original Message ----- 
From: "Dave Ewart" <davee@ceu.ox.ac.uk>
To: "StataList" <statalist@hsphsun2.harvard.edu>
Sent: Friday, September 23, 2005 9:51 AM
Subject: st: Cannot get date fields to import from PostgreSQL via ODBC



> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> I wonder if someone has seen this before and has found a solution.  Date
> types do not appear to import correctly into Stata when loaded via ODBC
> from a PostgreSQL database.
>
> I created a table in a PostgreSQL database to demonstrate the following
> problem.  The table, as viewed from within a 'psql' console is:
>
> Column |           Type           | Modifiers
> - --------+--------------------------+-----------
> a      | date                     |
> b      | time without time zone   |
> c      | timestamp with time zone |
>
> The data consists of a single record:
>
> # select * from mytable;
>     a      |    b     |           c
> - ------------+----------+------------------------
> 2005-09-23 | 10:20:30 | 2004-01-01 19:27:03+00
> (1 row)
>
> When importing into Stata:
>
> . odbc describe mytable
>
> DataSource: pgstuff (query)
> Table:      mytable (load)
> - -------------------------------------------------------------------------------
> Variable Name                               Variable Type
> - -------------------------------------------------------------------------------
> a                                           date
> b                                           time
> c                                           timestamptz
> - -------------------------------------------------------------------------------
>
> That looks OK so far ...
>
> . odbc load, exec("select * from mytable")
>
> . list
>
>     +----------------------------------+
>     |         a          b           c |
>     |----------------------------------|
>  1. | 01jan1960   10:20:30   01jan1960 |
>     +----------------------------------+
>
> And it's all gone horribly wrong.  The dates import as '01jan1960' (a
> 'zero' value, I believe, but formatted as a date).
>
> Does anyone have any suggestions for figuring out what's going on here?
>
> System:
>
> Stata 9 / Linux amd64, on Debian Sarge AMD64
> odbc-postgresql 07.03.0200-5
>
> Dave.
> - -- 
> Dave Ewart
> davee@ceu.ox.ac.uk
> Computing Manager, Cancer Epidemiology Unit
> Cancer Research UK / Oxford University
> PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370
> Get key from http://www.ceu.ox.ac.uk/~davee/davee-ceu-ox-ac-uk.asc
> N 51.7518, W 1.2016
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.4 (GNU/Linux)
>
> iD8DBQFDNBZxbpQs/WlN43ARAkfhAJ474C3zJV2ulqvy9BfP+x7IBj6tIACdEDRy
> pnlZbyZDPztKYxIiIx+ko4g=
> =la+y
> -----END PGP SIGNATURE-----
> *
> *   For searches and help try:
> *   http://www.stata.com/support/faqs/res/findit.html
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/