Two different time zones

[stevevance]

There is a disagreement in the current time between the web interface and the variable in the query. The screenshot shows how the date picker ends on April 27, but the query goes to April 28, at 4:59 AM.

The date picker is using the localhost time and the query is using UTC time.

I tried all sorts of changes to Docker, including adding an environment variable -e TZ=America/Chicago, to no avail.

I would like to use the variables because I like the date picker, but for now I will revert to using Postgres keywords so I can fix the time range.

[ankane]

Hey @stevevance, the time zone for the date selector is determined by the time zone set in blazer.yml. Times are then converted to UTC before being passed to the database. April 28, 4:59 UTC is equivalent to April 27, 23:59 Central Time.

[stevevance]

Hi @ankane I set the time_zone property in blazer.yml to "Central Time (US & Canada)".

One distinguishing factor is that the time zone problem only exists when using the special query with the start_time and end_time fields that initiates the date chooser.

I also tried "America/Chicago" as the time zone variable, which is the time zone that PostgreSQL uses, but it didn't work.

[ankane]

Hey @stevevance, I'm not sure I understand your last comment. The time zone selector is working as designed / expected.

[stevevance]

This is my blazer.yml configuration file:

data_sources:
  main:
    url: <%= ENV["BLAZER_DATABASE_URL"] || ENV["DATABASE_URL"] %>

    # statement timeout, in seconds
    # none by default
    # timeout: 15

    # caching settings
    # can greatly improve speed
    # off by default
    # cache:
    cache:
      mode: all
      expires_in: 10 # min

    # wrap queries in a transaction for safety
    # not necessary if you use a read-only user
    # true by default
    # use_transaction: false

    smart_variables:
      # zone_id: "SELECT id, name FROM zones ORDER BY name ASC"
      # period: ["day", "week", "month"]
      # status: {0: "Active", 1: "Archived"}

    linked_columns:
      # user_id: "/admin/users/{value}"

    smart_columns:
      # user_id: "SELECT id, name FROM users WHERE id IN {value}"

# create audits
audit: true

# change the time zone
time_zone: "America/Chicago"

# email to send checks from
# from_email: [email protected]

# webhook for Slack
# slack_webhook_url: <%= ENV["BLAZER_SLACK_WEBHOOK_URL"] %>

check_schedules:
  - "1 day"
  - "1 hour"
  - "5 minutes"

# enable map
# mapbox_access_token: <%= ENV["MAPBOX_ACCESS_TOKEN"] %>

And these are the start_date and end_date values that Blazer creates (5 hours into the future for my time zone):

select time_created_at::date as date, count(*) count from addresses_tracking
WHERE time_created_at >= '2020-04-09T05:00:00+00:00' AND time_created_at <= '2020-05-09T04:59:59+00:00'
group by date
order by date;

[ankane]

I'm not sure where the disconnect is, but the results you're seeing above are expected for the dates April 9 - May 9.

[stevevance]

I am expecting that the date bounds would be written in the query like so: >= '2020-04-09T00:00:00' AND time_created_at <= '2020-05-09T23:59:59'.

[remixtj]

Hey @stevevance, the time zone for the date selector is determined by the time zone set in blazer.yml. Times are then converted to UTC before being passed to the database. April 28, 4:59 UTC is equivalent to April 27, 23:59 Central Time.

Hi @ankane and what timezone is used when not specified?

I had to specify UTC to have values aligned with DB ones (Oracle, with no timezone on datetime field)

[ankane]

Cleaning up issues (don't plan to make any changes here right now).

@remixtj It uses the Rails time zone by default, which is UTC if none is specified.