Custom Timestamp in Kibana is 2 hours off

azeiner · July 14, 2022, 1:29pm

I'm importing and converting data from a textfile into elastic via the PHP API which is provided by elastic.
Now i'm encountering that the data is 2 hours off (depending on summertime settings) in kibana, not when i'm selected the data native in my web application.

Now the question is how should i import the data so kibana is showing the data correctly?

My code - my kibana timestamp is "datetimestart"

 $importdata = ["data" => $header->data,
                "datetimestart" => $header->DateTimeStart,
                "datetimestop" => $header->DateTimeStop,
                "timestamp" => Carbon::now()->format("Y-m-d H:i:s"),
];
$response = $client->index($importdata ); // write headerdata to elastic

I know elastic is interpreting datetime as UTC but is this the case with all 3 datetime fields or only the timestamp field?
Do i have to convert the datetimestart to UTC or is this done automatically?
Do i have to add the Timezone and when how should this be done?

A workaround is to set kibana to show UTC and set the "now-range" to 2 hours ahead - but nevertheless now is incorrect and neither we nor our customers like this approach!

Kind regards Andy

leandrojmp · July 14, 2022, 1:33pm

All date fields in elasticsearch are in UTC regardless of the name you are using.

Elasticsearch expects a date time in UTC, if your date time string has time zone information it will convert it to UTC before storing, if your date time string does not have time zone information it will assume that the time is already in UTC.

You need to convert to UTC or add the timezone information to your date string.

azeiner · July 14, 2022, 2:05pm

Thank you very much, this helps a lot!

elasticforme · July 14, 2022, 4:26pm

Further add to this.

when elk saves time, it converts to UTC and kibana will convert it back to your localtimezone and hence it shows up at correct place.
for example 2022-07-14 11:21:00 -> convert to UTC is +5 hour
now kibana will convert to -5 and hence it will show at same place.

now problem is if you query this data via sql query it will shows up +5 hour = 16:21:00 and it will confuse the hell out of you. (atlest it did to me and all my user and I was tired explaining to everyone)
hence this is what I did.

I save two date-time field. One with auto convert to UTC and let kibana take care all.
second I added timezone=UTC and user can use that if they are pulling data via sql.
this way it still shows up at same timestamp.

I hope this make sense. I still get confuse many time.

azeiner · July 15, 2022, 3:45pm

Thank you so much for your post, this is exactly what i was thinking of - i've an application which selects the dates now in UTC which confuses the user as it confuses me ... sooo ... i will do what you did - i duplicate all datetimefields and whatever application needs what i'm prepared, thanks for this tipp!! Since i'm importing data with custom API and more data will be imported from other sources so it's always good to know there is at least "one timestamp to fit them all" ya ... thank you again!

azeiner · July 15, 2022, 4:11pm

So - i'm confused again ... when i'm not using "UTC" in the Advanced Settings of Stack Management->Kibana i get the UTC Timestamp as the original Time and the other Timestamp with +2 hours ???

e.g. the timestamp in the textfile is 12.12.2021 12:12:01
I'm using this two fields:

datetimestart: 12.12.2021 14:12:01 (original timestamp)
datetimestartutc: 12.12.2021 12:12:01 (converted timestamp)

I thought i can use the browser timezone settings now? or not?
Confused

elasticforme · July 15, 2022, 4:59pm

Looks like you on GMT+2 timezone
for advance setting. do not change and just keep browser setting. and kibana will use that.

when you create index pattern use datetimestart/datetimestop as your timestamp setting.

and datetimestartutc/datetimestoputc is only used for pulling out data.

is your event actually happen at jul 13, 2022@00:27.58 ?

elasticforme · July 15, 2022, 5:15pm

sorry didn't read your update on bottom.

here is what I do. I do not convert timestamp I just add timezone to it. and I do this via logstash and python

What it does is just add timezone. what it does is keep same time and add zone. and hence elasticsearch do not do any conversion and saves as is
sql query will use this "timestamp_obj_UTC"
status_achieved_timezone= pytz.timezone('UTC').localize(timestamp)

Here it has timezone=cst and hence elasticserach will change time to UTC and saves. and when retrive it changes back to CST and display (kibana will use this "timestamp_obj_CST"
status_achieved=pytz.timezone('America/Chicago').localize(timestamp)

below. event was actually happen at 7:05:03.000
This is from discover tab on kibana.

status_achieved                  status_achieved_timezone
Jul 15, 2022 @ 12:05:03.000	     Jul 15, 2022 @ 07:05:03.000

But when I do setup visualization. I use status_achieved and it puts this event at 7:05 (converts time to America/Chicago, -5) because in Elastic it knows that this is UTC time and it has to convert to browser time.

But if I use status_achieved_timezone on visualization then it will shows up on wrong place because it will convert that to browser timezone. and it will show up at 2:05:03.00 (-5)

Trust me it is confusing. as I get confuse by typing this. LOL

Do not covert time. just add timezone

logstash

date { match => ["status_achieved_timezone", "dd-MMM-yy HH:mm:ss", "ISO8601"]
        timezone => "Etc/UTC"
        target => "status_achieved_timezone"
   }

azeiner · July 17, 2022, 9:24am

Oh this makes sense - so i can still use the format like

and just add timezone? what i'm a little concerned is, how to add the Timezone when i user this PHP API provided by Elastic ...

maybe i've missed something since i can change the timezone of carbon in this array-based structure ... adding a timezone before ingest as an additional parameter seems to be the way to go if i understand you right so the optimal way of dealing with datetimes would be (summed up): adding timezone to a datetime so Elastic will convert it to UTC, when receiving the datetime (from whereever) it will converte the datetime to the correct format and datetime ...right? the thing is - we've got messurementdata where everybody is concerned about the exact date and espacially time when the test started and finished ... until now this was no problem since i used my own application but kibana is the other side of the coin ... thank you very much for your help, i relly appreciate this a lot!! kind regards andy

elasticforme · July 17, 2022, 3:44pm

correct.

that is what I am doing. same here interested in event exact date.
But all big data technology now a days uses UTC and retrieving converts them back.

azeiner · July 18, 2022, 6:06am

Thank you very much, i'll give that a try today.

azeiner · July 19, 2022, 10:39am

I did the testing and i read your post over and over again - i think it's not that different hopefulle from what i'm doing although i don't know what this really is ... let me explain.

i'm exactly telling the datetime object what it is

            $dateTime1 = \DateTime::createFromFormat('d.m.Y H:i:s', $date1,new DateTimeZone('Europe/Vienna'));
            $dateTime2 = \DateTime::createFromFormat('d.m.Y H:i:s', $date2,new DateTimeZone('Europe/Vienna'));

so this should be the timezone correct datetime which is correct when i read the logs ...

now - this timestamp imported to elastic looks good when i read the timestamps to my table ... everyhting fine ...

now i'm telling this timestamp that it should be an UTC timestamp and elastic is still fine with it

grafik

so - UTC is timezone - 2 which is correct ...

if i'm setting kibana to "browser" everything is converted to +2 hours which is odd ...

if i set it back to UTC - ok everything is fine again but the "NOW" line is now 2 hours off ...

should my datetimes look somehow different? or how to i know what kibana/elastic are doing with it ...

can elastic run in a different timezone as the operating system? this could be an issue maybe ...

another question: is elastic doing the conversion of the timestamp or do i have to insert UTC?

i'm not sure how elastic is doing this conversion when i look at the timestamp or selecting data the timestamps are exactly as viewed eg.

azeiner · July 21, 2022, 11:51am

i just wanted to post the solution which worked for me - i made a big mistake when creating the datetime mappings

                        'datetimestart' => [
                            'type' => 'date',
                            "format" => "yyyy-MM-dd HH:mm:ss"

                        ]

which lead to the problem that i could not assign a timezone to this datetime (from what the error parser told me).

i removed the format and just let the "date" as a type

                    'datetimestart' => [
                        'type' => 'date',
                    ]

now i took all the advices and recreated all datetimes with the timezone AND assigned my datetime field including the timezone which can be done in PHP/Laravel with Carbon.

So with Carbon this looks like

$dateTime1 = Carbon::createFromFormat('d.m.Y H:i:s',$date1)->timezone('Europe/Vienna');

for the Elastic i assigned the object with the ISO 8601 date Format

$header->DateTimeStart =$dateTime1->format('c');

And now things are different, suddently Elastic recognizes which timezone the timestamp was saved in and shows the correct dates in the datetime fields, both in kibana and my own application ... seems you have always add the timezone in a way elastic can deal it!

grafik

now i've an UTC Timestamp and the regular Timestamp in my data which is fine, just in case. Thank you everybody for helping me out!

system · August 18, 2022, 11:52am

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