pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddate arithmetic with columns
5+ messages / 3 participants
[nested] [flat]
* date arithmetic with columns
@ 2012-02-29 19:50 Peter Faulks <faulksp@iinet.net.au>
0 siblings, 2 replies; 5+ messages in thread
From: Peter Faulks @ 2012-02-29 19:50 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: date arithmetic with columns
@ 2012-02-29 23:21 Peter Faulks <faulksp@iinet.net.au>
parent: Peter Faulks <faulksp@iinet.net.au>
1 sibling, 1 reply; 5+ messages in thread
From: Peter Faulks @ 2012-02-29 23:21 UTC (permalink / raw)
To: pgsql-sql
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?
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: date arithmetic with columns
@ 2012-03-03 10:24 Pavel Stehule <pavel.stehule@gmail.com>
parent: Peter Faulks <faulksp@iinet.net.au>
0 siblings, 0 replies; 5+ messages in thread
From: Pavel Stehule @ 2012-03-03 10:24 UTC (permalink / raw)
To: Peter Faulks <faulksp@iinet.net.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: date arithmetic with columns
@ 2012-03-04 09:45 hari.fuchs@gmail.com
parent: Peter Faulks <faulksp@iinet.net.au>
1 sibling, 1 reply; 5+ messages in thread
From: hari.fuchs@gmail.com @ 2012-03-04 09:45 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: date arithmetic with columns
@ 2012-03-04 11:29 Peter Faulks <faulksp@iinet.net.au>
parent: hari.fuchs@gmail.com
0 siblings, 0 replies; 5+ messages in thread
From: Peter Faulks @ 2012-03-04 11:29 UTC (permalink / raw)
To: pgsql-sql
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?
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-03-04 11:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-29 19:50 date arithmetic with columns Peter Faulks <faulksp@iinet.net.au>
2012-02-29 23:21 ` Peter Faulks <faulksp@iinet.net.au>
2012-03-03 10:24 ` Pavel Stehule <pavel.stehule@gmail.com>
2012-03-04 09:45 ` hari.fuchs@gmail.com
2012-03-04 11:29 ` Peter Faulks <faulksp@iinet.net.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox