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 (Kevin Crow - StataCorp)
Subject   Re: st: Re: Cannot get date fields to import from PostgreSQL via ODBC
Date   Wed, 28 Sep 2005 09:43:30 -0500


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.

> 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
> Computing Manager, Cancer Epidemiology Unit
> Cancer Research UK / Oxford University
> PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370
> Get key from
> N 51.7518, W 1.2016
> Version: GnuPG v1.2.4 (GNU/Linux)
*   For searches and help try:

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