Postgres Pro
Downloads
Home   >   mailing lists

Thread: date arithmetic with columns

date arithmetic with columns

From
Peter Faulks
Date:
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?

Re: date arithmetic with columns

From
Peter Faulks
Date:
Bit more googling and I came up with:

r.utc + CAST( tz.diffmins || ' ' || 'minutes' AS interval)

It works, but is it the best way?

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?
>

Re: date arithmetic with columns

From
Pavel Stehule
Date:
Hello

2012/3/1 Peter Faulks <faulksp@iinet.net.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

Re: date arithmetic with columns

From
hari.fuchs@gmail.com
Date:
Peter Faulks <faulksp@iinet.net.au> writes:

> 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.

Maybe I'm missing something, but why don't you just use timestamp with
timezone instead?

Re: date arithmetic with columns

From
Peter Faulks
Date:
Good question.

I'm porting a (never actually finished) app from Firebird to Postgres. 
Now that I've re-read how the timestamptz (which Firebird doesn't have) 
actually works, I think I'll change the tables and get rid of the 
timezone lookup.

Thanks

On 4/03/2012 8:45 PM, hari.fuchs@gmail.com wrote:
> Peter Faulks<faulksp@iinet.net.au>  writes:
>
>> 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.
>
> Maybe I'm missing something, but why don't you just use timestamp with
> timezone instead?
>
>