Adding a numeric column to a timestamp column truncates result

[Keith]

In my database, I have one table that stores sessions with a start time using TIMESTAMP. A second table stores events that are related to a particular session as an offset from the start time in fractional seconds.

CREATE TABLE Session
(
  id INT PRIMARY KEY,
  ...
  starttime TIMESTAMP NOT NULL,
  ...
)

CREATE TABLE Event
(
  id INT PRIMARY KEY,
  ...
  offset DOUBLE NOT NULL,  -- this is in seconds
  session_id INT NOT NULL,
  ...
)

I try doing the following query:

SELECT s.starttime + e.offset SECOND
FROM session s
JOIN event e on e.session_id=s.id;

This works, and I get back a timestamp, but the fractional part of the seconds is truncated to zero. I've tried other forms of my query like

SELECT s.starttime + INTERVAL '1' SECOND * e.offset ...

and

SELECT s.starttime + cast(e.offset as INTERVAL SECOND) ...

and I get a timestamp, but the fractional seconds is always truncated.

Now here's the interesting part, if I do this

SELECT TIMESTAMP '2017-05-24 00:00:00' + 0.5 SECOND FROM session;

then I get back a timestamp WITH the fractional seconds intact.

Why are the seconds being truncated when I try to combine columns?

I am using version 2.4.0.

[Fred Toussi]

Use this:

offset INTERVAL SECOND(6) NOT NULL

[Keith]

Ah, ok. That works. Adapting your answer, I kept the offset defined as a DOUBLE as tried this:

SELECT s.starttime + INTERVAL '1' SECOND(6) * e.offset ...

and it worked! Strangely,

SELECT s.starttime + cast(e.offset as INTERVAL SECOND(6)) ...

and

SELECT s.starttime + e.offset SECOND(6) ...

still result in truncation.

[Fred Toussi]

There was an issue with the fractional part based on a DOUBLE value has been fixed for 2.4.1