search

influxdata / telegraf

Agent for collecting, processing, aggregating, and writing metrics, logs, and other arbitrary data.
https://influxdata.com/telegraf
MIT License
14.63k stars 5.58k forks source link

[FEATURE] ability to control type of datetime column in postgresql output #13747

Closed jcpunk closed 1 year ago

jcpunk commented 1 year ago

Use Case

Currently the postgreql column for datetime is hard coded to PgTimestampWithoutTimeZone. This is problematic when I'm attempting to use timezone specific date sources.

Expected behavior

Ability to specify timestamp column should contain a timezone. And the ability to force a specific timezone (ie UTC or America/Chicago) to be used. When reading in unix timestamps the documented behavior for telegraf is to omit the timezone which can lead to data confusion.

Actual behavior

Hard coded to contain no timezone

Additional info

Location of hard coded data type: https://github.com/influxdata/telegraf/blob/master/plugins/outputs/postgresql/datatypes.go#L56

Note on query issues:

If a time zone is specified in the input for time without time zone, it is silently ignored.

from https://www.postgresql.org/docs/current/datatype-datetime.html

srebhan commented 1 year ago

@jcpunk I'm not completely sure I can follow the problem you have with timezones. Currently, the time column is always in UTC (see code) so the stored time is always representing the time of your metric.

Is there any specific reason you do not want to use UTC for storing the data? I strongly recommend against this as you easily run into issues with day-light-saving time-zones...

jcpunk commented 1 year ago

I can live with converting my messages to UTC. The thing generating them insists on using local time.

With the time column omitting the timezone, the resulting query always ignores the timezone parameter from https://www.postgresql.org/docs/current/datatype-datetime.html. I'm not sure why no time zone was chosen for the column type, but with my queries using the timezone that does cause some data confusion.

srebhan commented 1 year ago

I can live with converting my messages to UTC.

You don't need to do this as the output does it for you. It takes the metric timestamp and stores it in UTC in the database. So in my understanding, all you need to do is to make sure the timestamp in your queries is also UTC...

jcpunk commented 1 year ago

Unfortunately I don't have great control over the query being sent. It really wants to use local time and expects the database to "do the right thing". With the column dropping the timezone, my return results are usually about 6 hours off due to my local TZ.

srebhan commented 1 year ago

Understood. Will care for it.

srebhan commented 1 year ago

@jcpunk please test PR #13763! It also contains the time-column renaming PR (#13750).