Mismatch between time of encounter and its event_record


I’ve noticed an encounter record(in encounter table) whose date_created is greater the date_created in the event_records table. I’m wondering in which scenario this could be possible? Find the following screenshots which are queried on the production database.

Encounter date_created is 2019-03-30 22:37:34

Event records with above encounter uuid - you can see the date_created 2019-03-30 19:37:37 is less than above encounter date_created time

@angshuonline @mksrom @sumanmaity112 @snehabagri

1 Like

Update: All the records have same time mis-match. The time difference between encounter(date_created) and event_record(timestamp, date_created) has 3 hrs difference which exactly same as timezone difference between Jordan and UTC. Expected behavior is date_create from encounter and event_records should match.

Any thoughts on what could be cause?

Able to figure out the issue. The timestamp and date_created are of MySql TIMESTAMP type.

From the official MySql documentation it says

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.13, “MySQL Server Time Zone Support”.

Find more info in official docs

After changing the server time(to the same timezone from where I got the dump), I could see the event_record’s timestamp/date_created are same(or very close) to the actual encounter time.


Very nice detective work! Thanks @shivarachakonda for sharing the solution. :slight_smile: