How to convert date time format from EST to UTC?

[Dev999]

I have a date field in the format "2017-02-10T10:24:58.290-05:00", which means 10:24:58 in EST timezone. How do I convert it to straight UTC time "2017-02-10T15:24:58.290"? The data is already indexed so I can only do it at search time. There were some similar questions but I could not find a good way to do it. Do I miss something obvious here?

Thanks!

[gvmorley]

Hi,

Similar to many of the suggestions above, I'd go for something like this:

| makeresults
| fields - _time
| eval st = "2017-02-10T10:24:58.290-05:00"
| rex field="st" "(?<offset_h>[-+]\d{2}):(?<offset_m>\d{2})$"
| eval offset=((tonumber(offset_h)*60*60)+(tonumber(offset_m)*60))*-1
| eval st_epoch = strptime(st,"%Y-%m-%dT%H:%M:%S.%3f")
| eval st_local = strftime(strptime(st,"%Y-%m-%dT%H:%M:%S.%3f%:z"),"%Y-%m-%dT%H:%M:%S.%3f%:z")
| eval st_utc = strftime(st_epoch+offset,"%Y-%m-%dT%H:%M:%S.%3f")."-00:00"
| eval st_utc_epoch = strptime(st_utc,"%Y-%m-%dT%H:%M:%S.%3f%:z")

This will give you a variety of options to play with. Depending on what your requirement is, you could either pick the st_utc or the st_local field.

I'm currently in a (+07:00) timezone, so these are my results:

[Dev999]

This is essentially the same as my "convoluted" solution. In my opinion, it has general usage and should not take that much code to do such a trivial work. strptime does get the correct epoch time (UTC), just need an elegant way to convert it in any given time zone. Python added astimezone() method in dateutil (not datetime), so it has demand.

Thanks a lot for your time!

[sloshburch]

1. For data already indexed, you can use Eval's strptime OR the convert command to switch this to epoch. Once in epoch you can let Splunk represent it in the relative local timezone of the viewer OR always in EPOCH easily using Eval's strptime OR the convert.
2. If this is supposed to be the _time field, then make sure to update the sourcetype to properly extract this value, regardless of timezone, going forwarder. You can use the same Eval's strptime syntax in the sourcetype definition

Here's an example where I create a new field using your example set to st. Then I use the strptime syntax (which dynamically pulls the timezone). The value is then as epoch (which I believe is UTC/GMT by definition). Then display the utctime by using the epoch's timezone as a relative shifter.

[Dev999]

Thanks Burch. But here is exactly where my problem is. The utctime is still 10:24, not 15:24 as I would expect.
But I think that's how the date time is supposed to work, instead of changing the hours ,it uses a timezone field (%Z).

[sloshburch]

The utctime is the epoch time (which by definition, is relative to the epoch). I think the challenge here is that when I render the time back (using the convert command), it displays as the local time zone.

Here's how we can take the timezone as a relative adjuster to the time and shift what renders to UTC:

| makeresults 1
| fields - _time
| eval st = "2017-02-10T10:24:58.290-05:00"
| eval epoch_time = strptime( st, "%FT%T.%3N%:z" ), utc_time = relative_time( epoch_time , strftime( epoch_time , "%z" ) . "h" )
| convert ctime(utc_time)

I'll update the image in the first post to show this.

Alternatively, are you able to convert the other data (that is already in UTC) into the local time?

[Dev999]

It would not be able to handle Indian time, and not sure why it's 14:24, not 15:24?

It turns out strptime would convert to system locale:
|makeresults 1 | eval st = "2017-02-10T10:24:58.290+05:30"
| eval tt1 = strptime(st, "%Y-%m-%dT%H:%M:%S.%3Q%:z")
| eval tt2 = strftime(tt1, "%Y-%m-%dT%H:%M:%S.%3Q%:z")
| eval offset=strftime(tt1, "%:z")
| table st, tt2, offset

The result is surprising: offset: -5:00. tt2: "2017-02-09T23:54:58.290-05:00". So if my splunk server was set to GMT then it would be all set.

Creating a python command is much easier and does not require server settings change.

It would be nice if Splunk can provide a standard command like astimezone(epoch_time, zone_name) similar to that in python dateutil. It would only need a few lines of code.

Thanks!

[sloshburch]

What makes you say it won't be able to handle Indian time? I thought the 1 hour shift has to do with daylight savings time?

[Dev999]

Here is what I meant by convoluted way:) Gotta have a better solution...

| eval _tt1 = strptime(st, "%Y-%m-%dT%H:%M:%S.%3Q%:z") | eval _tt2 = strftime(_tt1, "%:z") | rex field=_tt2 "(?[+-])(?[^:]):(?[\d])" | convert num(hh) as hh | convert num(mm) as mm | eval sec = (hh*60*60 + mm*60) | eval offset=if(pm=="-", sec*(-1), sec) | eval _tt3 = _tt1 - offset | eval utc_time = strftime(_tt3, "%Y-%m-%d %H:%M:%S.%3Q")

[richgalloway]

This should get you started. It's not very portable, but gets the job done. Note that the "+5h" argument to relative_time needs to be the offset to UTC from the Splunk server's time zone, not the data's time zone.

eval utc=strftime(relative_time(strptime(est,"%Y-%m-%dT%H:%M:%S.%3N%z"),"+5h"),"%Y-%m-%dT%H:%M:%S.%3NZ")

[Dev999]

The value is from the data that is already indexed, and can be from different time zones ("-5:00" can be different values). I need to extract the field and make it a UTC time. I can make it work with a bunch of eval statements but seems too convoluted for such a simple common job.
Thanks for your response.

[richgalloway]

strptime automatically converts the time string from the specified offset to the server's local time. The offset from that to UTC will be constant.

[ECovell]

Just a toss in the dark here, could you try:

| eval time=strftime(_time, "%H/%M/%S")