New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL datetime field not showing properly or filtering properly #13627

Closed
SonicBlaze8 opened this Issue Oct 11, 2018 · 5 comments

Comments

Projects
None yet
4 participants
@SonicBlaze8

SonicBlaze8 commented Oct 11, 2018

Hi there,

I did not see this particular issue in the listings here.

I'm on version 5.3 of Grafana and utilizing MSSQL as the data source. Using Centos as the OS.

What I've done is a table query utilizing SQL code and the $__timefilter macro.

The scenario I'm seeing is that the datetime record is being interpreted by Grafana in a strange manner.

In SQL the EndDate (my datetime field) is showing as 2018-10-14 00:00:00.000 but Grafana shows it up as October 13th, 2018 8 PM. I have no idea how it's even doing this.

When I use the "This Week" date range filter I would expect this record to be excluded as the date in SQL is 2018-10-14 and the parameters for the date range filter show as 2018-10-07 00:00:00 to 2018-10-13 23:59:59.

Any help in understanding what I might be doing wrong here would be greatly appreciated.

@SonicBlaze8

This comment has been minimized.

Show comment
Hide comment
@SonicBlaze8

SonicBlaze8 Oct 11, 2018

Hi there

Additional information after a bit more digging.

My browser is set to Eastern Time and the server is in the UK.

Looking further at the coding I was able to find that the reason this October 14th record was included is because Grafana is putting the timezone range as:

con.EndDate BETWEEN '2018-10-07T04:00:00Z' AND '2018-10-14T03:59:59Z'

instead of what I would expect via SQL as

con.EndDate BETWEEN '2018-10-07T00:00:00Z' AND '2018-10-13T23:59:59Z'

This is what is causing an issue and the only work around at the moment is to put in a time shift on each and every panel and dashboard.

I have people using this from many different time zones and this is problematic to maintain especially with the day light savings time zone changes.

Also the system dashboard preferences for time zones do not seem to be taking effect as changing those to the different options results in no changes.

SonicBlaze8 commented Oct 11, 2018

Hi there

Additional information after a bit more digging.

My browser is set to Eastern Time and the server is in the UK.

Looking further at the coding I was able to find that the reason this October 14th record was included is because Grafana is putting the timezone range as:

con.EndDate BETWEEN '2018-10-07T04:00:00Z' AND '2018-10-14T03:59:59Z'

instead of what I would expect via SQL as

con.EndDate BETWEEN '2018-10-07T00:00:00Z' AND '2018-10-13T23:59:59Z'

This is what is causing an issue and the only work around at the moment is to put in a time shift on each and every panel and dashboard.

I have people using this from many different time zones and this is problematic to maintain especially with the day light savings time zone changes.

Also the system dashboard preferences for time zones do not seem to be taking effect as changing those to the different options results in no changes.

@torkelo

This comment has been minimized.

Show comment
Hide comment
@torkelo

torkelo Oct 12, 2018

Member

Make sure the date in your MSSQL database is stored in UTC format. Grafana expects date columns to be in UTC

Member

torkelo commented Oct 12, 2018

Make sure the date in your MSSQL database is stored in UTC format. Grafana expects date columns to be in UTC

@torkelo torkelo closed this Oct 12, 2018

@Joe2099

This comment has been minimized.

Show comment
Hide comment
@Joe2099

Joe2099 Oct 13, 2018

Did this behavior change between 5.3.0 and prior versions? My data is stored in a DATETIME field which does not have a timezone associated with it (unlike TIMESTAMP). This used to work fine, but now as of 5.3.0 the filtering of data using $__timefilter() is offset by the timezone difference....

Joe2099 commented Oct 13, 2018

Did this behavior change between 5.3.0 and prior versions? My data is stored in a DATETIME field which does not have a timezone associated with it (unlike TIMESTAMP). This used to work fine, but now as of 5.3.0 the filtering of data using $__timefilter() is offset by the timezone difference....

@marefr

This comment has been minimized.

Show comment
Hide comment
@marefr

marefr Oct 15, 2018

Member

Duplicate of #13621

Member

marefr commented Oct 15, 2018

Duplicate of #13621

@marefr marefr marked this as a duplicate of #13621 Oct 15, 2018

@SonicBlaze8

This comment has been minimized.

Show comment
Hide comment
@SonicBlaze8

SonicBlaze8 Oct 15, 2018

Update from my end, it's exactly as torkelo and marefr have indicated, that it was the UTC format requirement.

I was unaware of this need. I've been able to resolve my specific need based on this information and changed the settings for each dashboard and panel to utilize the UTC date format.

Thank you for all your assistance with this.

SonicBlaze8 commented Oct 15, 2018

Update from my end, it's exactly as torkelo and marefr have indicated, that it was the UTC format requirement.

I was unaware of this need. I've been able to resolve my specific need based on this information and changed the settings for each dashboard and panel to utilize the UTC date format.

Thank you for all your assistance with this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment