Ok I needed a ::timestamptz at time zone 'UTC' and a >= :)
On 17 October 2017 at 22:29, Glenn Pierce <glennpierce@xxxxxxxxx> wrote:
> Hi so I have a simple table as
>
> \d sensor_values_days;
> Table "public.sensor_values_days"
> Column | Type | Modifiers
> -----------+--------------------------+------------------------------
> ts | timestamp with time zone | not null
> value | double precision | not null default 'NaN'::real
> sensor_id | integer | not null
> Indexes:
> "timestamp_id_index" UNIQUE CONSTRAINT, btree (ts, sensor_id)
> Foreign-key constraints:
> "sensor_values_days_sensor_id_fkey" FOREIGN KEY (sensor_id)
> REFERENCES sensors(id)
>
>
> and I have a simple query that fails
>
> Ie
>
> SELECT sensor_id, MAX(ts), date_trunc('day', ts), COALESCE(MAX(value),
> 'NaN')::float FROM sensor_values_days WHERE ts > '2017-10-06
> 00:01:01+00' AND ts < '2017-10-06 23:59:59+00' GROUP BY 1, 3 ORDER BY
> 1, 2;
> sensor_id | max | date_trunc | coalesce
> -----------+-----+------------+----------
> (0 rows)
>
>
> If I remove the timezone part of the start date I get results.
>
> Ie
>
> SELECT sensor_id, MAX(ts), date_trunc('day', ts), COALESCE(MAX(value),
> 'NaN')::float FROM sensor_values_days WHERE ts > '2017-10-06 00:01:01'
> AND ts < '2017-10-06 23:59:59+00' GROUP BY 1, 3 ORDER BY 1, 2;
> sensor_id | max | date_trunc | coalesce
> -----------+------------------------+------------------------+----------
> 597551 | 2017-10-06 01:00:00+01 | 2017-10-06 00:00:00+01 | 13763
> 597552 | 2017-10-06 01:00:00+01 | 2017-10-06 00:00:00+01 | 8168
> 597553 | 2017-10-06 01:00:00+01 | 2017-10-06 00:00:00+01 | 9441
> ....
> ...
> ..
>
> I'm sure I am doing something silly but can't see what.
> Does anyone know what is going on here ?
>
> I am using Postgres 9.5
>
> Thanks
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
