Thread: Cast: timestamp to integer
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>
select cast(to_char(current_timestamp, 'd') as integer)/5; On 12/06/03, Michal Durys (joe@anime.com.pl) wrote: > I'd like to convert timestamp and date fields to intergers. > Unfortunately cast() says it's impossible: -- Rory Campbell-Lange <rory@campbell-lange.net> <www.campbell-lange.net>
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) >
Why not use EXTRACT ? Arjen > -----Oorspronkelijk bericht----- > Van: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] Namens Rory Campbell-Lange > Verzonden: donderdag 12 juni 2003 18:58 > Aan: Michal Durys > CC: pgsql-general@postgresql.org > Onderwerp: Re: [GENERAL] Cast: timestamp to integer > > > select cast(to_char(current_timestamp, 'd') as integer)/5; > > On 12/06/03, Michal Durys (joe@anime.com.pl) wrote: > > I'd like to convert timestamp and date fields to intergers. > > Unfortunately cast() says it's impossible: > -- > Rory Campbell-Lange > <rory@campbell-lange.net> > <www.campbell-lange.net> > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > http://archives.postgresql.org
On Thu, 12 Jun 2003, 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 believe EXTRACT has an "epoch" unit that is seconds from 1970 as a double which you can cast to an int.
seems: select cast(extract(epoch from current_timestamp) as integer); is much simplier. 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. > >