Difference between timestamp variable and time variable

Question

I have the following table in postgresql (table1):

timestamp1  timestamp without time zone NOT NULL,
variable1 integer,
timestamp2  timestamp without time zone NOT NULL

I want calculate the timestamp2.

Note that variable 1 is of type integer in table 1, but in practice is a time variable defined in hours

The timestamp2 is defined by difference of timestamp1 and variable1 (timestamp2= timestamp1 – variable1)

For example,

2013-02-06 07:00:00  -  5 = 2013-02-06 02:00:00
2013-02-06 09:00:00  - 12 = 2013-02-05 21:00:00 
2013-02-06 12:00:00  - 4.5  = 2013-02-06 07:30:00

How to do this calculation (of timestamp2) in postgresql?

Clodoaldo Neto · Accepted Answer · 2014-03-18 19:21:20Z

2

select timestamp1 - interval '1 hour' * variable1
from table1

answered Mar 18, 2014 at 18:49

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mig8 · Accepted Answer · 2014-03-18 18:47:49Z

1

Postgres understands the number as time:

answered Mar 18, 2014 at 18:47

mig8

1224 bronze badges