1

SELECT count(*) AS orders
FROM orders
WHERE created_at >= ‘{{ date.start }}’ AT TIME ZONE ‘utc’ AT TIME ZONE ‘eet’
AND created_at <= ‘{{ date.end }}’ AT TIME ZONE ‘utc’ AT TIME ZONE ‘eet’

when selected interval from 2019-12-01 to 2019-12-05 it count data from 2019-12-01 to 2019-12-04 so what cause this ???

2

Without looking at your data, I have several suggestion for you:

  1. You also have to define the time zone for your created_at column using the AT TIME ZONE on the WHERE clause.

  2. You can use BETWEEN to define a date range, because BETWEEN is a shorthand for >= AND <=.

  3. I also noticed that you defined the time zone twice, first for the ‘utc’ and the second is for ‘eet’, is there any reason why? In my experience, I always define it once.

Example:
WHERE created_at AT TIME ZONE ‘eet’ BETWEEN ‘{{date.start}}’ AT TIME ZONE ‘eet’ AND ‘{{date.end}}’ AT TIME ZONE ‘eet’

  1. If your created_at granularity is set at time, then you had to date_trunc() to day it first or using date() if you are using QRDS. The date range selector (assuming you are using date range instead of datetime range) will select the date on the 00:00 hour. Because:
    2019-12-05 07:00 is not between
    2019-12-01 00:00 and
    2019-12-05 00:00
    right?

Example:
WHERE date_trunc(‘day’, created_at) ...........
Or
WHERE date(created_at) ..........
The rest … is the same as the first example

Tips: you can create query snippet to speed up your query writing when you create the date range query parameter. I believe, it’s one of the most often used parameter when we creating the query.

1 Like