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

[lachg258]

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:

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

[svetb]

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

Global variables

[lachg258]

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

[svetb]

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