3 hour time difference between Kibana reports and PostgreSQL database

Elastic Stack Elasticsearch
21

hi David,
very nice, so the secret is to use "size":1, the rest of the DSL query I knew ...
here is the document output for you:

Blockquote
{
"took" : 493,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 10000,
"relation" : "gte"
},
"max_score" : 2.0,
"hits" : [
{
"_index" : "access_logs",
"_type" : "access_log",
"_id" : "48vCtnUBQXUwojsCLuQF",
"_score" : 2.0,
"_source" : {
"terminalid" : 45,
"id" : 14115,
"tagcode" : "512941",
"tagtype" : 1,
"accessdeniedreason" : null,
"accesspermited" : true,
"@timestamp" : "2020-11-11T10:03:00.472Z",
"controllerid" : 15,
"employeetagid" : 894,
"facecaptureid" : null,
"employeeid" : 886,
"direction" : 0,
"accesstime" : "2020-08-17T06:05:54.133Z",
"@version" : "1"
}
}
]
}
}

22

and for the mapping, here it is too:

{
"access_logs" : {
 "mappings" : {
   "properties" : {
     "@timestamp" : {
       "type" : "date"
     },
     "@version" : {
       "type" : "text",
       "fields" : {
         "keyword" : {
           "type" : "keyword",
           "ignore_above" : 256
         }
       }
     },
     "accessdeniedreason" : {
       "type" : "text",
       "fields" : {
         "keyword" : {
           "type" : "keyword",
           "ignore_above" : 256
         }
       }
     },
     "accesspermited" : {
       "type" : "boolean"
     },
     "accesstime" : {
       "type" : "date"
     },
     "controllerid" : {
       "type" : "long"
     },
     "direction" : {
       "type" : "long"
     },
     "employeeid" : {
       "type" : "long"
     },
     "employeetagid" : {
       "type" : "long"
     },
     "facecaptureid" : {
       "type" : "long"
     },
     "id" : {
       "type" : "long"
     },
     "tagcode" : {
       "type" : "text",
       "fields" : {
         "keyword" : {
           "type" : "keyword",
           "ignore_above" : 256
         }
       }
     },
     "tagtype" : {
       "type" : "long"
     },
     "terminalid" : {
       "type" : "long"
     }
   }
 }
}
}
23

Please format your code, logs or configuration files using </> icon as explained in this guide and not the citation button. It will make your post more readable.

Or use markdown style like:

```
CODE
```

This is the icon to use if you are not using markdown format:

image
image764×92 16.4 KB

There's a live preview panel for exactly this reasons.

Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.
Please update your post.

24

Could you share the equivalent line from PostgresQL of the document 48vCtnUBQXUwojsCLuQF which is in elasticsearch?

25

hi David, many thanks for explaining how to format my source code earlier on this forum:
I did that, using the markdown style, so this part is fixed! cheers, Bogdan

26

hi David, thanks for your message: I found the equivalent line from PostgreSQL of the document 48vCtnUBQXUwojsCLuQF which is in Elasticsearch with this nice SQL query:

  select * from   public."AccessLogs"  
  where "EmployeeId" = 886
  and "AccessTime" >= '17-Aug-2020'::date 
  and "AccessTime" < '18-Aug-2020'::date 
  order by "AccessTime" desc;
27

and these are the results (full results list!) I have received below:

14240	894	True	2020-08-17 12:47:35.038501	886	512941	0	38	1		15	
14204	894	True	2020-08-17 12:16:16.316952	886	512941	0	43	1		15	
14160	894	True	2020-08-17 10:40:17.472175	886	512941	0	42	1		15	
14151	894	True	2020-08-17 10:20:51.405527	886	512941	0	41	1		15	
14130	894	True	2020-08-17 09:19:48.238722	886	512941	0	40	1		15	
14129	894	True	2020-08-17 09:19:46.815926	886	512941	0	40	1		15	
14128	894	True	2020-08-17 09:19:06.44717	886	512941	0	40	1		15	
14125	894	True	2020-08-17 09:16:14.727044	886	512941	0	42	1		15	
14115	894	True	2020-08-17 09:05:54.133306	886	512941	0	45	1		15	
14093	894	True	2020-08-17 08:15:20.67465	886	512941	0	40	1		15
28

and you see, David, that the only row that possibly matches the Elastic document ID that you mentioned, is this row below:

14115	894	True	2020-08-17 09:05:54.133306	886	512941	0	45	1		15

so as you can see, there again appears a 3-hour offset here:
... continued ...

29

because you see, David, that this document ID returned by Elasticsearch below:

{
  "took" : 804,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : 2.0,
    "hits" : [
      {
        "_index" : "access_logs",
        "_type" : "access_log",
        "_id" : "48vCtnUBQXUwojsCLuQF",
        "_score" : 2.0,
        "_source" : {
          "terminalid" : 45,
          "id" : 14115,
          "tagcode" : "512941",
          "tagtype" : 1,
          "accessdeniedreason" : null,
          "accesspermited" : true,
          "@timestamp" : "2020-11-11T10:03:00.472Z",
          "controllerid" : 15,
          "employeetagid" : 894,
          "facecaptureid" : null,
          "employeeid" : 886,
          "direction" : 0,
          "accesstime" : "2020-08-17T06:05:54.133Z",
          "@version" : "1"
        }
      }
    ]
  }
}

shows the access time as this:
"accesstime" : "2020-08-17T06:05:54.133Z",

30

so David, I think we are very close to the solution with the above analysis:
please help us to understand and pinpoint the exact root cause for this!
thanks & greetings from Bucharest, Bogdan

31

Here is what I did.

I pasted 2020-08-17 09:05:54 (Bucharest TZ) in a TZ converter.

And I got: 2020-08-17T06:05:54 UTC Timezone.

So here is what I can say from this. Everything looks good from Elasticsearch point of view. The date indexed in elasticsearch is 2020-08-17 09:05:54 GMT+3 which is correct.

So there is nothing wrong here.

I can see that you now see a problem in Kibana, right?
Knowing that there's no issue in the date representation in Elasticsearch, I'd suggest to either move this question to #elastic-stack:kibana or open a new question (which I'd encourage you to do) to see if there is an option or something wrong.

Remember that Kibana AFAIK is using the browser configuration to display data.

32

hi David, thanks for your post earlier, regarding the analysis you have done, to show that there is no issue on the date representation in Elasticsearch: this is very good to hear, so we will close this issue here: thanks again and greetings from Bucharest!

33

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.