Re: date arithmetic with columns

Hello

2012/3/1 Peter Faulks <faulksp(at)iinet(dot)net(dot)au>:
> Bit more googling and I came up with:
>
> r.utc + CAST( tz.diffmins || ' ' || 'minutes' AS interval)
>
> It works, but is it the best way?
>

r.utc + tz.diffmins * interval '1 minute'

regards

Pavel Stehule

>
> On 1/03/2012 6:50 AM, Peter Faulks wrote:
>>
>> I have two columns in two distinct tables, one is the starting time of
>> an event, timestamp without time zone. Data is the utc datetime (for
>> sorting across time zones), the other is the number of minutes to add.
>>
>> I am migrating from Firebird. One of the queries uses the dateadd
>> function to build a local starting time thus:
>>
>> SELECT CAST(DATEADD(tz.diffmins MINUTE TO r.utc) AS TIME) AS ts
>> FROM races r JOIN tracks t ON t.trk = r.trk
>> JOIN timezones tz on tz.state = t.state....
>>
>> The equivalent postgres would be along the lines of
>>
>> SELECT r.utc + INTERVAL '480 minutes'
>>
>> How can I substitute the hard-coded 480 for the tz.diffmins?
>>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql