Frustration with date/times/epoch in v7.3.

From: Mike Benoit <mikeb(at)netnation(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Frustration with date/times/epoch in v7.3.
Date: 2002-12-11 18:33:44
Message-ID: 1039631623.12433.34.camel@mikeb.staff.netnation.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Table:

Column | Type | Modifiers
-------------------+-----------------------+---------------------------------------------------------------------
imported_date | integer | not null default 0

PG v7.2.1 (nice and clean):

select imported_date, "timestamptz"(imported_date) from server_accounts limit 1;
imported_date | timestamptz
---------------+------------------------
1037498593 | 2002-11-16 18:03:13-08
(1 row)

PG v7.3 (nasty and dirty):

select imported_date, "timestamptz"(imported_date) from server_accounts limit 1;
ERROR: Bad timestamp external representation '1027966107'

select imported_date, "timestamptz"( cast(imported_date as timestamp) ) from server_accounts limit 1;
ERROR: Cannot cast type integer to timestamp without time zone

select imported_date, "timestamptz"( cast(imported_date as timestamptz) ) from server_accounts limit 1;
ERROR: Cannot cast type integer to timestamp with time zone

select imported_date, ('Jan 1 1970'::DATE + (imported_date || ' seconds')::INTERVAL)::timestamp from server_accounts limit 1
imported_date | timestamp
---------------+---------------------
1027966107 | 2002-07-29 18:08:27

The last query works, but you must admit it is pretty nasty. Yes, I could create a from_epoch() function that takes care of this, but should this really be nessecary?

Is there a better way to go about this in v7.3 without changing the column type?

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Copeland 2002-12-11 19:44:42 Re: [INTERFACES] Patch for DBD::Pg pg_relcheck problem
Previous Message Ross J. Reedstrom 2002-12-11 17:59:40 psql's tab completions for ALTER command