Extract day or hour from time field in InfluxDB with InfluxQL

Question

I would like to extract parts of a date from the timestamp field in InfluxDB like hour, day, month etc. This will be helpful to create a heatmap plot in Grafana.

Target state:

time	day	month	hour
2024-01-02 04:25:00	2	1	4
2023-12-25 16:25:00	25	12	16

I have to use InfluxQL, so Flux or SQL are not an option.

• date_part would be ideal but is only available in flux or SQL
• date modification functions don't seem to exist in InfluxQL

Options I considered, but haven't figured out: manual calculation or string operations, so pointers would be helpful.

Answer 1

In InfluxQL, the query language of InfluxDB, unfortunately does not support date part extraction directly. This is a limitation of InfluxQL and currently, there are no built-in functions to extract the hour, minute, or day from a timestamp.

As a workaround, you can export the data and process the timestamps in a programming language that can handle date and time manipulations, such as Python or JavaScript. After extracting the required parts, you can add these as new fields to your data and import it back into InfluxDB.

The workaround involves exporting and importing data.

In Python, you can convert the InfluxDB timestamp to a datetime object and then extract the required parts. Here’s a simple example:

from datetime import datetime, timezone

# Assuming `ts` is your timestamp from InfluxDB
ts = 1578152602608558363

# Convert the timestamp to a datetime object
dt = datetime.fromtimestamp(ts / 1e9, timezone.utc)

# Extract the parts
day = dt.day
month = dt.month
hour = dt.hour