Bug 2060613

Description of problem:

Not sure if this is a regression or if this specific component was not considered on https://bugzilla.redhat.com/show_bug.cgi?id=1709902

When querying for job-invocations and filtering by date, the query is done using UTC times and not the local time zone of the user.

Version-Release number of selected component (if applicable):

Observed on Satellite 6.9.8

How reproducible:


Steps to Reproduce:

1. Having a job invocation associated with a task having the following properties: 

~~~
#  select id,started_at,start_at,action from foreman_tasks_tasks where id = 'c790768a-60a6-4c1a-bccf-74419654a376';
-[ RECORD 1 ]--------------------------------------------
id         | c790768a-60a6-4c1a-bccf-74419654a376
started_at | 2022-03-03 00:30:37.21
start_at   | 2022-03-03 00:30:37.21
action     | Run hosts job: Install errata RHSA-2021:3572
~~~

Being on timezone EST (-0500) and running this query, returns nothing.

~~~
#  hammer -u admin -p password job-invocation list --search 'YESTERDAY' --order "id asc"
---|-------------|--------|---------|--------|---------|-------|-------|---------------------|-------
ID | DESCRIPTION | STATUS | SUCCESS | FAILED | PENDING | TOTAL | START | RANDOMIZED ORDERING | INPUTS
---|-------------|--------|---------|--------|---------|-------|-------|---------------------|-------
~~~

Note that the "2022-03-03 00:30:37.21 UTC" is "2022-03-02 19:30:37.21 -0500" and should be returned to the query, as it is "yesterday" for the local user.

Looking at the sql logs on foreman, this is the kind of filter I see, simply filtering by the day and not taking in consideration timezones.

~~~
("foreman_tasks_tasks"."started_at" >= '2022-03-02' AND "foreman_tasks_tasks"."started_at" < '2022-03-03') OR ("foreman_tasks_tasks"."start_at" >= '2022-03-02' AND "foreman_tasks_tasks"."start_at" < '2022-03-03') OR ("foreman_tasks_tasks"."ended_at" >= '2022-03-02' AND "foreman_tasks_tasks"."ended_at" < '2022-03-03')
~~~

2. Having this task data:

~~~
# select id,started_at,start_at,action from foreman_tasks_tasks where id = 'c790768a-60a6-4c1a-bccf-74419654a376';
-[ RECORD 1 ]--------------------------------------------
id         | c790768a-60a6-4c1a-bccf-74419654a376
started_at | 2022-03-02 00:30:37.21
start_at   | 2022-03-02 00:30:37.21
action     | Run hosts job: Install errata RHSA-2021:3572
~~~

Being on timezone EST (-0500) and running this query, returns jobs from 2 days ago(2022-03-01)

~~~
#  hammer -u admin -p password job-invocation list --search 'YESTERDAY' --order "id asc"
------|-------------------------------|--------|---------|--------|---------|-------|--------------------------
ID    | DESCRIPTION                   | STATUS | SUCCESS | FAILED | PENDING | TOTAL | START                    
------|-------------------------------|--------|---------|--------|---------|-------|--------------------------
30701 | Install errata RHSA-2021:3572 | failed | 0       | 4      | 0       | 4     | 2022-03-01 19:30:37 -0500
------|-------------------------------|--------|---------|--------|---------|-------|--------------------------
~~~

Actual results:

Returns wrong results.

Expected results:

Should take in consideration local time for queries.

Additional info: