Constraint violation for timestamp without timezone

[nathansgreen]

OS: FreeBSD 11.1
PostgreSQL: 9.6.6
TimescaleDB: 0.6.0

I was copying data from a regular table into a TimescaleDB table via INSERT... SELECT... WHERE ts >= $1 AND ts < $2 and got the following error:

ERROR:  new row for relation "_hyper_1_40_chunk" violates check constraint "constraint_15"
DETAIL:  Failing row contains (31485671542, 2017-09-21 19:00:00, 545, 777710, 'z').

I tracked down the constraint to this expression:

(("ts" >= '2017-09-22 00:00:00'::timestamp without time zone)
 AND ("ts" < '2017-09-23 00:00:00'::timestamp without time zone))

I'll also note that my server's time zone is CST (currently UTC-05:00). Both tables define ts as timestamp without time zone, and when I select from the source table, I indeed see the timestamp exactly as it appears in the error message. Where it gets weird is, I can single out this particular row and insert it just fine:

INSERT INTO process SELECT * FROM old_process WHERE id = 31485671542;

What exactly is going on here? Is TimescaleDB using the server time zone to determine which chunk the row goes into? I don't understand why my second statement works just fine when the only difference is the number of rows returned by the SELECT.

Also: a feature request. In the future, can the constraint creation process include the column name in the constraint name? Possibly the chunk name as well, since the constraint names end up repeating a lot. It would be much easier to track down the problem if I don't have to filter out a dozen constraints with identical names.

[nathansgreen]

Alright, I think I figured the problem: my local client defaults its timezone to UTC, but when I run psql from a shell on the server it uses CST. But when I select the row, it shows the proper timestamp, so I still don't understand why TimescaleDB is trying to stick it in the wrong hypertable.

[cevian]

@nathansgreen Thanks for the bug report. I've submitted #307 to fix this bug.