Thread: how to covert a column?

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