Thread: casting... adding integer to timestamp
If play_length is a timestamp, I can do this: SELECT play_length - INTERVAL '13 seconds' ... But what if play_seconds is a column? SELECT play_length - INTERVAL 'play_seconds seconds' ... This doesn't work. ERROR: invalid input syntax for type interval: "play_seconds seconds" Can anyone help? Thanks, Mark
On Jun 25, 2006, at 14:23 , Mark Gibson wrote: > SELECT play_length - INTERVAL 'play_seconds seconds' ... The column isn't interpolated into the string. Try SELECT play_length - play_seconds * INTERVAL '1 second' Hope this helps. Michael Glaesemann grzm seespotcode net
On Sat, 24 Jun 2006 23:23:57 -0600 Mark Gibson <mark@gibsonsoftware.com> thought long, then sat down and wrote: > > > If play_length is a timestamp, I can do this: > > SELECT play_length - INTERVAL '13 seconds' ... > > But what if play_seconds is a column? > > SELECT play_length - INTERVAL 'play_seconds seconds' ... > > This doesn't work. > > ERROR: invalid input syntax for type interval: "play_seconds seconds" > SELECT play_length - play_seconds... should do the trick, if play_sconds is of type interval. Otherwise you must explicitly cast it like SELECT play_length - play_seconds::interval... Regards, -- Frank Finner Invenius - Lösungen mit Linux Köpfchenstraße 36 57072 Siegen Telefon: 0271 231 8606 Mail: frank.finner@invenius.de Telefax: 0271 231 8608 Web: http://www.invenius.de Key fingerprint = 90DF FF40 582E 6D6B BADF 6E6A A74E 67E4 E788 2651
Attachment
Yet another way, this time being more explicit. SELECT play_length - play_seconds * '1 second'::interval Cheers. On Saturday 24 June 2006 22:23, Mark Gibson wrote: > If play_length is a timestamp, I can do this: > > SELECT play_length - INTERVAL '13 seconds' ... > > But what if play_seconds is a column? > > SELECT play_length - INTERVAL 'play_seconds seconds' ... > > This doesn't work. > > ERROR: invalid input syntax for type interval: "play_seconds seconds" > > Can anyone help? > > Thanks, > Mark > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
Michael Glaesemann wrote: > > On Jun 25, 2006, at 14:23 , Mark Gibson wrote: > >> SELECT play_length - INTERVAL 'play_seconds seconds' ... > > The column isn't interpolated into the string. Try > > SELECT play_length - play_seconds * INTERVAL '1 second' > That worked great! Thanks! Mark