Postgres Pro
Downloads
Home   >   mailing lists

Thread: Adding seconds to a time

Adding seconds to a time

From
"Rob Richardson"
Date:
I'm sure this has been answered so many times that the answers are buried somewhere in the millions of hits I get when I try to search for it.
 
I have a table whose rows contain the number of seconds since an event, and a foreign key into a second table that contains the actual time of the event.  How can I add the number of seconds to the event time to get the actual time a record was stored in the first table?
 
Thank you very much!
 
RobR
 
 

Robert D. Richardson
Product Engineer Software

RAD-CON, Inc.
TECHNOLOGY: Innovative & Proven
Phone : +1.440.871.5720 ... ext 123
Fax:  +1.440.871.2948
Website:  www.RAD-CON.com
E-mail:  rob.richardson@RAD-CON.com

 
Attachment

Re: Adding seconds to a time

From
"Rob Richardson"
Date:
And, of course, as soon as I post the question I find the answer, through a more intelligent search:
 
Multiply the number by a standard interval: 
    select N * '1 second'::interval
 
RobR
 

Re: Adding seconds to a time

From
"Jean-Yves F. Barbier"
Date:
On Wed, 15 Sep 2010 09:20:04 -0400, "Rob Richardson"
<Rob.Richardson@rad-con.com> wrote:


Be careful though, there might be a '+1x' shift:
SELECT now();                       => 2010-09-15 15:39:02.838245+02
see:
SELECT now() + '1 month'::interval; => 2010-10-15 15:39:27.518034+02

I guess that is an SQL rule, but it is not "legally" correct, when you
calculate a contract termination date for example (which should be
2010-10-14.)

> And, of course, as soon as I post the question I find the answer,
> through a more intelligent search:
>
> Multiply the number by a standard interval:
>     select N * '1 second'::interval
>
> RobR
>


--
I tried the clone syscall on me, but it didn't work.
        -- Mike Neuffer trying to fix a serious time problem

Re: Adding seconds to a time

From
Lew
Date:
(Top-posting corrected.)

Rob Richardson wrote:
>> And, of course, as soon as I post the question I find the answer,
>> through a more intelligent search:
>>
>> Multiply the number by a standard interval:
>>      select N * '1 second'::interval

Jean-Yves F. Barbier wrote:
 > Be careful though, there might be a '+1x' shift:
 > SELECT now();                       =>  2010-09-15 15:39:02.838245+02
 > see:
 > SELECT now() + '1 month'::interval; =>  2010-10-15 15:39:27.518034+02
 >
 > I guess that is an SQL rule, but it is not "legally" correct, when you
 > calculate a contract termination date for example (which should be
 > 2010-10-14.)

Of course it's legally correct.  One month after September 15 is October 15 by
anyone's definition.

The contract termination issue is not due to miscalculation of "plus one
month" but to mistaken inclusion of the +1 month date within the contract
period.  The contract needs to be understood as "from date X through but not
including X + '1 month'::interval'".  The date X + '1 month'::interval still
is what it is, e.g., October 15 in your example.

--
Lew

Re: Adding seconds to a time

From
"Jean-Yves F. Barbier"
Date:
On Thu, 16 Sep 2010 20:22:21 -0400, Lew <noone@lewscanon.com> wrote:

Yeah of course, I didn't say it correctly so I reformulate:
IN CASE OF financial|contract's datation, be careful :-)

> Of course it's legally correct.  One month after September 15 is October
> 15 by anyone's definition.
>
> The contract termination issue is not due to miscalculation of "plus one
> month" but to mistaken inclusion of the +1 month date within the contract
> period.  The contract needs to be understood as "from date X through but
> not including X + '1 month'::interval'".  The date X + '1
> month'::interval still is what it is, e.g., October 15 in your example.
>


--
Above all else -- sky.