Re: How to Substract Milliseconds from A timestamp(3) Field

["Brandon Aiken" <BAiken(at)winemantech(dot)com>]

Ah, right.  Mea culpa.

SELECT (end_time - interval '00:00:00.005') AS work_timestamp FROM
mytable;

That should work, too.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: Steve Crawford [mailto:scrawford(at)pinpointresearch(dot)com] 
Sent: Tuesday, October 17, 2006 5:14 PM
To: pgsql-novice(at)postgresql(dot)org
Cc: Brandon Aiken; Lane Van Ingen
Subject: Re: [NOVICE] How to Substract Milliseconds from A timestamp(3)
Field

Brandon Aiken wrote:
> SELECT (end_time - interval '00:00:05') AS work_timestamp FROM
mytable;



No, that will subtract 5 seconds, not 5 milliseconds. Just use subtract
'0.005 seconds'::interval:


select now(), now()-'0.005 seconds'::interval;
              now              |           ?column?
-------------------------------+-------------------------------
 2006-10-17 14:11:59.805446-07 | 2006-10-17 14:11:59.800446-07

Cheers,
Steve




> 
> --
> Brandon Aiken
> CS/IT Systems Engineer
> 
> -----Original Message-----
> From: pgsql-novice-owner(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Lane Van Ingen
> Sent: Tuesday, October 17, 2006 2:33 PM
> To: pgsql-novice(at)postgresql(dot)org
> Subject: [NOVICE] How to Substract Milliseconds from A timestamp(3)
> Field
> 
> In PLPGSQL, does anyone know how to subtract MILLISECONDS from a field
> defined as timestamp(3), yielding another field defined as
timestamp(3)?
> 
> Have been trying interval, and conversion functions but no luck.
> 
> Example: '2006-10-17 14:23:28.951'; subtract 5 milliseconds from a
field
> 
>    called mytable.end_time, and return the results to a field named
>    work_timestamp.
> 
> work_timestamp := ????