postgresqlmilliseconds

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


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


Solution

  • 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.