If the server is using other timezone the JDBC repository will show wrong time. about jsr352

[chengfang]

Can you elaborate why this is a postgresql-specific problem? Steps and configurations to reproduce it?

[grigoras-cristinel]

I'm using postgresql an h2 databases. The h2 database is inside server and in the same timezone.
I very possible to happen on all SQL servers but i don't know for others.
To reproduce error change the running wildlfy instance timezone value from command line like -Duser.timezone=UTC and try to clean jdbc repository for job executions older than by example 3 hours using sql.

[chengfang]

I changed WildFly timezone to be UTC, and also ran my postgres database server with the default timezone. When inserting new job execution data (including created time, end time, etc) into jdbc job repository, the assumed timezone is the one from WildFly runtime (e.g., UTC). If you query the job repository directly with sql (tools like psql), it will display date values that are correct if interpreted with UTC (WildFly timezone), but incorrect in the database server's timezone.

postgres=# select jobexecutionid, endtime, batchstatus from job_execution;
 jobexecutionid |         endtime         | batchstatus
----------------+-------------------------+-------------
              1 | 2018-06-05 03:23:55.448 | COMPLETED
(1 row)

From the above sql output, the endtime is 2018-06-05 03:23:55, which is tomorrow morning in my timezone (also my postgres db timezone) and hence seems incorrect. But it is the correct timestamp in my WildFly server's timezone (UTC).

If you have your batch client query the job data through java api (e.g., JobOperator.getJobExecution(), etc), the timestamps are displayed correctly.

curl -s http://localhost:8080/restAPI/api/jobexecutions/1 | python -m json.tool
{
    "batchStatus": "COMPLETED",
    "createTime": 1528169035355,
    "endTime": 1528169035448,
    "executionId": 1,
    "exitStatus": "COMPLETED",
    "href": "http://localhost:8080/restAPI/api/jobexecutions/1",
    "jobInstanceId": 1,
    "jobName": "restJob1",
    "jobParameters": {},
    "lastUpdatedTime": 1528169035453,
    "startTime": 1528169035371
}

The above createTime 1528169035355 is Mon 4 June 2018
23:23:55, which is the correct local time I ran this test.

[chengfang]

If using timestamp with timezone, the date value is displayed correctly in psql output in postgres server's timezone:

postgres=# \d job_execution
                                               Table "public.job_execution"
     Column      |           Type           | Collation | Nullable |                        Default
-----------------+--------------------------+-----------+----------+-------------------------------------------------------
 jobexecutionid  | bigint                   |           | not null | nextval('job_execution_jobexecutionid_seq'::regclass)
 jobinstanceid   | bigint                   |           | not null |
 version         | integer                  |           |          |
 createtime      | timestamp with time zone |           |          |
 starttime       | timestamp with time zone |           |          |
 endtime         | timestamp with time zone |           |          |
 lastupdatedtime | timestamp with time zone |           |          |
 batchstatus     | character varying(30)    |           |          |
 exitstatus      | character varying(512)   |           |          |
 jobparameters   | character varying(3000)  |           |          |
 restartposition | character varying(255)   |           |          |
Indexes:
    "job_execution_pkey" PRIMARY KEY, btree (jobexecutionid)
Foreign-key constraints:
    "fk_job_execution_job_instance" FOREIGN KEY (jobinstanceid) REFERENCES job_instance(jobinstanceid) ON DELETE CASCADE
Referenced by:
    TABLE "step_execution" CONSTRAINT "fk_step_exe_job_exe" FOREIGN KEY (jobexecutionid) REFERENCES job_execution(jobexecutionid) ON DELETE CASCADE

postgres=# select jobexecutionid, endtime, batchstatus from job_execution;
 jobexecutionid |          endtime           | batchstatus
----------------+----------------------------+-------------
              1 | 2018-06-05 11:02:48.313-04 | COMPLETED

[chengfang]

A job repository database may hold job data from multiple WildFly instances that may be in different timezone. Therefore, it makes sense to use timestamp with timezone in postgres to be able to view all date values in consistant and predictable timezones.

[chengfang]

Cloned this issue to JIRA: https://issues.jboss.org/browse/JBERET-419

[chengfang]

f0551b5