How to visualize data using the time field from the raw data, as opposed to the Timestamp?

[phildefer]

Hello, I am a beginner with Splunk. I am experimenting with a csv dataset containing the daily average temperature for different cities across the world. As a first step, I would like to see, for a given city, the graph for the average temperature over time. However by default, the X axis on the timechart shows the timestamp of the source file, as opposed to the time field contained in each event. As a result, all events show the same date, which is probably the date the dataset was created.

How do I use the "Date" field contained in each event, instead of the Timestamp of the dataset file?

Thanks,

[bowesmana]

As @tscroggins says, it's always important to get your ingest dates correctly extracted from the data in the first place.

However, to extract a time from a field in the data you use the strptime() function, e.g.

| eval _time=strptime(date_field, "format_string")

which will overwrite the existing _time field with the time converted from your data field called date_field according to the format string you specify.

Time format variables are documented here

https://docs.splunk.com/Documentation/Splunk/9.1.1/SearchReference/Commontimeformatvariables

e, g. this example, which you can paste into your search bar will convert the string in my_date_field to _time.

| makeresults
| eval my_date_field="2023-11-13 08:01:02.123"
| eval _time=strptime(my_date_field, "%F %T.%Q")

Note that times are converted to epoch times, but the _time field is special in that it will show you the formatted date, rather than the epoch.

[tscroggins]

Hi @phildefer,

I would normally recommend extracting the timestamp correctly when the data is indexed, but if you've uploaded the csv file as a lookup file, your approach would differ.

How are you searching the data? How is the Date field formatted?

[phildefer]

Date has YYYY-MM-DD format. I managed changing the '_time'  field by using  the command:
eval _time=strptime(Date,"%Y-%m-%d")

Now the Time column in the events list shows the date in the dd/mm/yyyy, with the actual time of 00:00:00.000

[tscroggins]

As @bowesmana noted, this is the way. The timestamp is time zone-aware, though, so be mindful of the offset. If you prefer, you can include a time zone in your conversion, e.g. as a shortcut for for UTC:

| eval _time=strptime(Date."Z", "%Y-%m-%d%Z")