Cast: timestamp to integer

From: Michal Durys <joe(at)anime(dot)com(dot)pl>
To: pgsql-general(at)postgresql(dot)org
Subject: Cast: timestamp to integer
Date: 2003-06-12 16:44:55
Message-ID: 20030612184455.577d0a6e.joe@anime.com.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I hope it's not a question for psql-novice. ;-)

I'd like to convert timestamp and date fields to intergers.
Unfortunately cast() says it's impossible:

ERROR: Cannot cast type date to integer
ERROR: Cannot cast type timestamp without time zone to integer

I'm quite sure it should be possible somehow. After all timestamp is
(AFAIR) number of seconds counted from 1970.

I need it for example to caclulate average number of downloads of a
given file a day. For the time being I came up with an ugly solution
like this:

SELECT id, name, counter, counter/(extract(years FROM age(date_add))*365
+ extract(months FROM age(date_add))*30 + extract(days FROM
age(date_add)) + 1) AS ratio FROM dnload_files WHERE counter>0;

I will be much obliged for any suggestions.

Thanks in advance.
--
Joe <joe(at)anime(dot)com(dot)pl>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mike Mascari 2003-06-12 16:45:44 Re: Temp tables, indexes and DELETE vs. TRUNCATE
Previous Message MT 2003-06-12 16:39:20 Best pg_dump practices