CODEWITHSUNDEEP
postgresqltimescaledb
Noggio
Noggio

Reputation: 31

Why do I need to typecast every timestamp?

I am using PostgreSQL 12.6 with TimescaleDB, and I have a hypertable with a "time" field like this :

"time" timestamp without time zone NOT NULL

And when I try to work with "time", I need to typecast it like this.

SELECT COUNT(*) FROM raws WHERE "time"::date BETWEEN '2016-04-19 15:16:00' AND '2021-04-19 15:16:00'

The problem is I am working on a existing codebase and my collegue doesn't need to typecast. Is it a setting I am missing, or do we really need to typecast ?

EDIT: I forgot to mention that the table is an hypertable. I imported all the data from a dump, this is one of the data I got for "time" :

2021-04-08 00:00:00

The query with casting gets me around a million results, but without casting I get 0 results.

I tried to reindex the hypertable but I got this :

REINDEX TABLE raws;
ERROR:  chunk not found

Upvotes: 0

Views: 274

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247280

With that cast to date, PostgreSQL will convert the string literal '2021-04-19 15:16:00' to date to match the data type on the left side. As a result, the hours, minutes and seconds are discarded.

See the type conversion rules for functions for a detailed reference.

So it seems that there are rows with "time" equal to 2016-04-19 00:00:00, but none with 2016-04-19 15:16:00.

Upvotes: 1

Related Questions