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

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


From   Dave Ewart <davee@ceu.ox.ac.uk>
To   StataList <statalist@hsphsun2.harvard.edu>
Subject   st: Cannot get date fields to import from PostgreSQL via ODBC
Date   Fri, 23 Sep 2005 15:51:29 +0100

-----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/



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