Extract the date out of datetime

Fluxlang
1

Hey,

i have a column with timestamps in the following form
image
What i need is to extract the date out of this in the following form:
yyyy-mm-dd
Is there a way to achive this?
Thank you in advance

2
3

i tried it with the date.truncate() function but i couldn’t define a column.
What do i have to do to define a column?

4

Hi @Patse,
How about something like this:

import "date"

from(bucket: "Jetson")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "exec_jetson_stats")
  |> filter(fn: (r) => r["_field"] == "jetson_CPU1")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> map(fn: (r) => ({ r with month_day: date.truncate( t: r._time, unit: 1d) }))
  |> yield(name: "mean")

In my example, I take the _time field and map it to a new column called month_day. I use date truncate on each row to remove the time values apart from the year, month and day.

5

@Patse There is a way, but it is admittedly a bit cumbersome. You can create a custom function that uses functions in the date and strings packages:

import "date"
import "strings"

fmtDate = (t) => {
    year = string(v: date.year(t: t))
    month = if strings.strlen(v: string(v: date.month(t: t))) < 2 then "0${date.month(t: t)}" else string(v: date.month(t: t))
    day = if strings.strlen(v: string(v: date.monthDay(t: t))) < 2 then "0${date.month(t: t)}" else string(v: date.monthDay(t: t))

    return "${year}-${month}-${day}"
}

You can then use map() to iterate over each row and generate the custom timestamp in a new column:

// ...
    |> map(fn: (r) => ({ r with formattedDate: fmtDate(t: r._time) })

The output data will include a new formattedDate column with the date in the format you need (string).

There is a feature request/proposal for a date.format() function that you can see here: [feature request] date.format function · Issue #3473 · influxdata/flux · GitHub. This isn’t available yet, but you’re welcome to track to the issue and/or provide feedback

2 Likes