My Table looks like this:
Inventory
inventory | creationDate
{} | 2017-04-27 14:15:15.25+02
{} | 2017-04-27 13:03:02.205+02
{} | 2017-04-27 13:03:01.766+02
{} | 2017-04-27 13:02:19.8+02
{} | 2017-04-27 12:35:52.12+02
The Query:
SELECT * FROM "Inventory"
WHERE "registerTillInventoryId" = 1
AND "creationDate" <= '2017-04-27 12:02:38.000 +00:00'
ORDER BY "creationDate" DESC;
The Result:
inventory | creationDate
{} | 2017-04-27 13:03:02.205+02
{} | 2017-04-27 13:03:01.766+02
{} | 2017-04-27 13:02:19.8+02
{} | 2017-04-27 12:35:52.12+02
My question is why i get the data from
13:03:02.205+02 and 13:03:01.766+02
The result of my query should be
inventory | creationDate
{} | 2017-04-27 13:02:19.8+02
{} | 2017-04-27 12:35:52.12+02
'2017-04-27 12:02:38.000 +00:00' is '2017-04-27 14:02:38.000 +00:02',
so it returns all but '2017-04-27 14:15:15.25+02' as it the only value that does not comply "creationDate" <= '2017-04-27 12:02:38.000 +00:00'
which is right. example:
t=# with a as (select '2017-04-27 12:02:38.000 +00:00' at time zone 'utc' ts)
select ts, ts at time zone 'gmt+2' from a;
ts | timezone
---------------------+------------------------
2017-04-27 12:02:38 | 2017-04-27 14:02:38+00
(1 row)
Time: 0.480 ms
why not just "creationDate" <= '2017-04-27 12:02:38.000' to avoid mixing tz?..
Okay thank you for the Help. I fiqured out the problem with the help of the Comment of Vao Tsun.
What i didn't descriped was that i tried to get 13:02 from 12:02 +00:00
I do not really need the Timezones. So if I query
creationDate < '2017-04-27 13:02:38.000'
directly. Without Timezones i get my right result.
{} | 2017-04-27 13:02:19.8+02
{} | 2017-04-27 12:35:52.12+02
...