Question 89765 in PostgreSQL Question posted in
The official documentation can be found here.

How to add or subtract milliseconds with calculated value or from a field? – Postgresql

In Postgresql, I try to subtract 5 milliseconds
(using fixed constant, when I search on Google, most of example using fixed constants)

SELECT ([field start_date] - interval '00:00:00.005') AS work_timestamp
from mytable

How to change ‘5’ or ’00:00:00.005′ form query above
with a calculated process or from a field value

with a field value (field ms_value) :

SELECT ([field start_date] - interval '00:00:00.00'+[field ms_value]) AS work_timestamp
from mytable

with a calculated process (field ms_value1 and ms_value2 or other calculated process) :

SELECT ([field start_date] - interval '00:00:00.00'+[field ms_value1-field ms_value2]) AS work_timestamp
from mytable

Thank you

Tags:

4

Answers


  1. 0

    The solution I usual use is Concatenates the values with its unit, afterthat cast it to Interval.

    i.e:

    select now(), now() - ('5'|| ' ms')::interval

    In your case will be

    SELECT ([field start_date] - ('00:00:00.00' || [field ms_value])::interval AS work_timestamp
from mytable

SELECT ([field start_date] - ('00:00:00.00' || ([field ms_value1-field ms_value2]))::interval AS work_timestamp
from mytable

SELECT ([field start_date] - (([field ms_value1-field ms_value2]) || ' ms')::interval AS work_timestamp
from mytable
    Login or Signup to reply.
  2. 0

    Just do a quick calculation:

    SELECT field - (INTERVAL ’1 millisecond’ * 5) FROM table_name;
    Login or Signup to reply.
  3. 0

    Avoid concat. Multiply by a constant of 1 millisecond seems better

    select now(), now() - (5 * interval '1  ms')

    if 5 is provided externally

    select now(), now() - ($1 * interval '1  ms')

    5 could potentially be (field ms_value1- field ms_value2) in your final example.

    Login or Signup to reply.
  4. 0

    You can use make_interval()

    select start_date - make_interval(secs => ms_value/1000.0)

select start_date - make_interval(secs => (ms_value1-ms_value2)/1000.0)

    Online example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search