Timestamp Causing Date Discrepancies

Grafana Time Series Panel
1

Hi folks,

This is my first time trying to set up a time-series graph and I’m having issues with the date / timestamp field. For whatever reason, when I query for timestamp(date), it shifts the date back to the previous day, e.g., if the date is 9/1, the timestamp function returns 8/31 instead of 9/1. Screenshot attached where raw_dt represents the raw date and timestamp_dt represents the timestamp function conversion. I suspect it’s related to timezone conversions (UTC vs ET). Does anyone know how to fix the query so that it returns the correct date? Thank you!

Screenshot 2024-09-19 at 5.32.50 PM
Screenshot 2024-09-19 at 5.32.50 PM1157×513 27.2 KB

2

if your timestamp in your database UTC?

3

My database is in Eastern but I couldn’t figure out how to convert the timestamp function from UTC to ET.

4

so grafana requires timestamps to be UTC to be accurately ploteed so no need to convert it.

not sure it matters if your server is whatever time zone as long as the database table timestamp column is UTC

5

We want the graph to reflect the timezone in our database, though, so I need to convert the timestamp to Eastern in order to show the correct data. You’re saying that Grafana only allows UTC timestamps which means we may need to use a different kind of graph.

6

so you are still not answering the original question.

in your database is the date time column contain UTC date?

7

I answered earlier in my first comment; pasting it below for reference. My database only has a date column but the time series panel in Grafana requires a timestamp field.

My database is in Eastern but I couldn’t figure out how to convert the timestamp function from UTC to ET.

8

:laughing: it still does not answer what time zone your database datetime column is

9

Eastern time zone. Based on time of year, this varies and can fall under EST or EDT. Does that help?

1 Like
10

depending on your database technology you can use functions such as convert_tz to nudge it to become UTC

is your database mysql, postgres or ms sql?

11

MySQL, but we want the data to mirror what we have in the database and reflect the same time zone. We do not want to use UTC. Maybe I can use convert_tz to shift over the timestamp() function.

1 Like
12

you might not want UTC but by using convert_tz it will convert it to UTC which grafana requires to accurately display it in time series.

using convert_tz does not change your data in your database it just shifts the timestamp to make grafana happy. otherwise you will have issues if the datetime from your db is not UTC

13

Sounds like time series won’t work for non-UTC time zones. I think I’ll just use a different graph. Thank you!

14

non UTC will work but not the way you want it. :peace_symbol: