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
> [email protected]
> 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/
