Remove the time component and only leave the date of the Timestamp

Grafana MySQL
1

Hi guys,

So I am querying the SETTLEMENTDATE column of my database (BIDDAYOFFER) and I only want to query the date and not the time component.
For example I only want to query “2021-05-24” instead of “2021-05-24 09:30:00”.

This is my current query:
SELECT
CAST(SETTLEMENTDATE AS Date) AS “time”,
PRICEBAND1, PRICEBAND2, PRICEBAND3, PRICEBAND4, PRICEBAND5, PRICEBAND6, PRICEBAND7, PRICEBAND8, PRICEBAND9, PRICEBAND10
FROM BIDDAYOFFER
WHERE DUID = “SUNRSF1” AND SETTLEMENTDATE = CURDATE()
ORDER BY LASTCHANGED DESC
LIMIT 1

Where the following table is being outputted on my graph:

img7
img7928×136 9.22 KB

Could someone please help me edit my query so that only the date part of “SETTLEMENTDATE” is outputted in my grafana.

1 Like
2

Yes that should be possible. I actually only recently found out that you can provide a custom date format, like ${__from:date:YYYY-MM-DD} when using the to and from variables. I haven’t used it myself but should work in theory. Check out

4

It looks good I am just not sure how to implement this variable in my query, could you please help me with that.

5

Ok, I think I slightly misunderstood what you’re trying to do. You’re talking about your query’s output, rather than using a date in the query itself.

Try to set the format to time: YYYY-MM-DD. See Grafana 7 new Table wont format timestamp as time · Issue #24917 · grafana/grafana · GitHub

6

This topic was automatically closed after 365 days. New replies are no longer allowed.