I don't have a completely satisfactory solution for this, but you could
do something like this:
SELECT id, name, counter, counter/extract(days FROM
current_timestamp - date_add) + 1) AS ratio FROM dnload_files WHERE counter>0;
This is because the timestamp substraction will return you the result in
days. But this is still a hack.
Cheers,
Csaba.
On Thu, 2003-06-12 at 18:44, Michal Durys wrote:
> 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@anime.com.pl>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>