Postgres Pro
Downloads
Home   >   mailing lists

Thread: porting time calcs to PG

porting time calcs to PG

From
greg@bryantrecording.com
Date:

I'm trying to port an MS statement that's a bit involved with
timestamps, and I don't see anything in the docs to lead me forward.
It's basically a select statement, looking for records with a timestamp
within a certain range, where that range is calculated with one of the
fields.  The WHERE clause that I use in SQL Server is:
getdate() + ((2100 + 5 + (9*Points)) / 86400) >= DueTime

Where the numbers are actually parameters passed in to the function.
Other than changine getdate() to now(), I'm not sure how to change the
+ interval to be effective.  All the docs I see use something like
interval '1 hour' - not sure how to put a calculated value in the
quotes.  Is this possible?

Re: porting time calcs to PG

From
Bricklen Anderson
Date:
greg@bryantrecording.com wrote:
>
> I'm trying to port an MS statement that's a bit involved with
> timestamps, and I don't see anything in the docs to lead me forward.
> It's basically a select statement, looking for records with a timestamp
> within a certain range, where that range is calculated with one of the
> fields.  The WHERE clause that I use in SQL Server is:
> getdate() + ((2100 + 5 + (9*Points)) / 86400) >= DueTime
>
> Where the numbers are actually parameters passed in to the function.
> Other than changine getdate() to now(), I'm not sure how to change the
> + interval to be effective.  All the docs I see use something like
> interval '1 hour' - not sure how to put a calculated value in the
> quotes.  Is this possible?
>
For the interval part, you can try
interval '1 minute' * <some number>

Re: porting time calcs to PG

From
Andrew Sullivan
Date:
On Thu, Dec 07, 2006 at 04:44:35PM -0700, greg@bryantrecording.com wrote:
> fields.  The WHERE clause that I use in SQL Server is:
> getdate() + ((2100 + 5 + (9*Points)) / 86400) >= DueTime
>
> Where the numbers are actually parameters passed in to the function.
> Other than changine getdate() to now(), I'm not sure how to change the
> + interval to be effective.  All the docs I see use something like
> interval '1 hour' - not sure how to put a calculated value in the
> quotes.  Is this possible?

Sure.  Something like

SELECT CURRENT_TIMESTAMP + (((2100 + 5 + (9*"Points")) /
86400) || 'seconds')::interval >= "DueTime"

oughta work.

--
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.
        --Roger Brinner