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

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


From   kcrow@stata.com (Kevin Crow - StataCorp)
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Re: Cannot get date fields to import from PostgreSQL via ODBC
Date   Wed, 28 Sep 2005 09:43:30 -0500

Dave,

Stata does not have a concept of time, therefore, all of the time or
datetime/timestamp variables might be read in as strings or the time will get
truncated.  For example,

. 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, table(dates) dsn(odbctest)

. list

     +----------------------------------+
     |         a          b           c |
     |----------------------------------|
  1. | 23sep2005   10:20:30   01jan2004 |
     +----------------------------------+

. describe

Contains data
  obs:             1
 vars:             3
 size:            28 (99.9% of memory free)
-------------------------------------------------------------------------------
              storage  display     value
variable name   type   format      label      variable label
-------------------------------------------------------------------------------
a               double %td
b               str8   %9s
c               double %td
-------------------------------------------------------------------------------
Sorted by:
     Note:  dataset has changed since last saved

In my example the only variable that does not get read in correctly is the
timestamp variable (the time was truncated).

I am not able to reproduce the bug here, but it does look like a bug because
you where able to query the database using the -iodbc- client and get the
correct results from the  PostgreSQL driver.  Do you know what version of the
PostgreSQL driver you are using?

> So, at the 'describe' stage, Stata is perfectly aware of the correct data
>types for each field.

Also, when you use the -odbc exec- command Stata is simply displaying
information giving to it as a string by the odbc driver.  Stata is not "aware"
of the time/date data types of PostgreSQL.



Kevin C.
StataCorp

> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Well, I found out something rather interesting related to this.
> 
> Dates, times and date/time-stamps do not import correctly via ODBC from
> a PostgreSQL database;
> 
> However, with a MySQL database, although times and date/time-stamps do
> not import correctly, plain dates *do* import correctly.
> 
> /me goes off to investigate the odbc-postgresql driver.
> 
> 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)
> 
*
*   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