Search Postgresql Archives

Re: how to covert a column?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Dimitrius Weddington wrote:
> I have a dataset (90GBs worth) that contains 3 columns of 
> unix timestamps in seconds. Ideally, I want to use copy to 
> load the data into the DB (the disk i/o of trying to process 
> this volume of data vi perl takes a couple of days). I was 
> hoping to convert the unix timestamps inside the DB to pg 
> timestamp creating a view or create table as... however no 
> success so far. The "to_timestamp()" function works fine to 
> convert the data but for some reason I can't get it to work 
> in creating a view with the fields that are timestamps instead of INT.

The following works well on my PostgreSQL 8.2.4 which is configured
with --enable-integer-datetimes:

SHOW TIME ZONE;
   TimeZone    
---------------
 Europe/Vienna
(1 row)

CREATE TABLE t (id integer NOT NULL PRIMARY KEY, ts integer);
CREATE VIEW v (id, ts) AS (SELECT t.id, to_timestamp(t.ts) FROM t);
INSERT INTO t (id, ts) VALUES (1, 0), (2, 3600);

SELECT * FROM v;
 id |           ts           
----+------------------------
  1 | 1970-01-01 01:00:00+01
  2 | 1970-01-01 02:00:00+01
(2 rows)

So your problem must be something else.

Maybe you can describe your problem in greater detail?

Yours,
Laurenz Albe


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux