| 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: | Whole Thread | Raw Message | 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>
| 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 |