Stata The Stata listserver
[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

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


From   "Kevin Crow - StataCorp" <kcrow@stata.com>
To   <statalist@hsphsun2.harvard.edu>
Subject   st: Re: Cannot get date fields to import from PostgreSQL via ODBC
Date   Mon, 26 Sep 2005 14:20:25 -0500

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/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index