Unexpected behavior with date fields in Elasticsearch

[victor_gonzalez]

I'm encountering an unexpected behavior with date fields in Elasticsearch. After converting date fields to text fields in SQL Server and indexing them in Elasticsearch, we noticed that Elasticsearch seems to adjust the dates incorrectly.

Despite separating the dates into year, month, and day using SQL Server functions and storing them as text fields, Elasticsearch seems to advance the day starting from 7 PM. This behavior is unexpected and causes inaccuracies in our data representation.

Steps to Reproduce:

1. Convert date fields to text fields in SQL Server using functions like CONVERT.
2. Index the text fields in Elasticsearch.
3. Observe the date representation in Kibana.

Expected Behavior:

I expect the date fields, stored as text in Elasticsearch, to retain their original values without any adjustments to the time.

Actual Behavior:

Elasticsearch seems to advance the day starting from 7 PM for the text fields representing dates. This results in inaccurate date representations in our data visualization tools.

Additional Information:

• I have verified that the date fields are correctly separated into year, month, and day before indexing.
• We have checked the timezone settings in both Elasticsearch and Kibana, ensuring they are configured correctly.

Request for Assistance:

I would appreciate any insights or guidance on how to resolve this issue. Additionally, if anyone has encountered a similar problem or has suggestions on how to troubleshoot and address this issue, we would be grateful for your assistance.

Thank you for your help and support.

[leandrojmp (Leandro Pereira)]

Hello and welcome,

All dates in Elasticsearch are stored as UTC, if you do not provide a timezone when indexing a date string, it will assume that the date is already in UTC.

When visualizing the data in Kibana, it will per default convert the date from UTC to the browser timezone, which will be the timezone from your operating system.

This sometimes can lead to confusions like what you described.

Please, share some example as the date string you are indexing, how you are seeing it in Kibana and also inform in which timezone are you.

[victor_gonzalez]

Hi Leandro,

Thank you for responding. In my database, I have a field called ‘creation_date.’ From this date, I segmented it using the functions to add new fields ex: year(creation_date) , month(creation_date) , and day(creation_date) in MSSQL Server. However, when I ingest the data into Elasticsearch, the ‘day’ field increments, especially for records associated with a creation date around 7 PM. It appears as if the inserted record belongs to the next day. Unfortunately, due to limitations on my Elasticsearch forum account, I cannot upload images to show more explicit examples. Also i'm using an elasticsearch 7.15 version.

In theory, the ‘day’ field should be received with its original value according to the data type, but I cannot explain why this situation is occurring

I appreciate your assistance.

[leandrojmp (Leandro Pereira)]

Hello,

You need to provide the information asked.

Please, share some example as the date string you are indexing, how you are seeing it in Kibana and also inform in which timezone are you.

You didn't share an example of your date string, nor how you are indexing it in elasticsearch and what timezone you are.

[victor_gonzalez]

Hi Leandro,

My time zone is bogota/colombia

fields:

creation_date -> datetime

year(creation_date) -> int ,
month(creation_date) -> int ,
day(creation_date) -> int

Also i tried to parse those fields in varchar and char, to ensure to get string values.

Unfortunately, due to limitations on my Elasticsearch forum account, I cannot attach images to show more explicit examples.

My Email is victorgr9@gmail.com

[leandrojmp (Leandro Pereira)]

@victor_gonzalez

My time zone is bogota/colombia

Bogota/Colombia is on UTC-5, so what you are seeing is consistent with the way dates works in Elasticsearch.

As mentioned, all date fields in Elasticsearch will be stored in UTC, so it is expected that all date fields being sent to Elasticsearch are already in UTC or have the timezone offset information.

When this is not the case you will have wrong dates in Elasticsearch, to avoid this you need to convert your date to UTC before sending it or send the timezone offset in the date string.

If you send just 2024-03-18 to elasticsearch, it will assume that this is 2014-03-18 00:00:00Z, which is not the case, and then Kibana will convert this back to the browser timezone which in your case will be UTC -5.

So, in this case it will convert to 2024-03-17 19:00:00, which is the equal to UTC minus 5.

You didn't say how you are indexing your data, just the format, but what are you using to send the data to elasticsearch?

You need to fix this in the ingestion by telling Elasticsearch that your date is not on UTC.

You will need to send your date string as something like this, 2023-03-18 00:00:00-05:00 and not just the month.