Search Postgresql Archives

Re: Simple query fail

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux