Change format of t-stamp in sql query

Ignition
1

Hello. In a Vision application (Ignition 8.1.25) I have a bar chart component that displays the result of a sql (mssql) query, which returns the top 8 rows. The x-axis ticks are labelled with the t-stamp under each bar. I would like to only display the hour and minute (HH:mm) of the t-stamp, and not the entire date/time, but I'm having trouble figuring out how the change the query or otherwise format the time. Any assistance or guidance would be appreciated. The current query is:

SELECT TOP (8)   t_stamp, CaseCount_L5Pack_Hourly  FROM HourlyCaseCount 
ORDER BY t_stamp desc

Which returns:

2024-12-12 09:00:00.000,196
2024-12-12 08:00:00.007,166
2024-12-12 07:00:00.000,163
2024-12-12 06:00:00.000,159
2024-12-12 05:00:00.000,153
2024-12-12 04:00:00.003,172
2024-12-12 03:00:00.000,175
2024-12-12 02:00:00.000,167

I tried adding a convert command, but while that changed the format, it also changed the data.

SELECT TOP (8)  convert(varchar, t_stamp, 8)  t_stamp, CaseCount_L5Pack_Hourly  FROM HourlyCaseCount 
ORDER BY t_stamp desc

Returns:

23:00:00,137
23:00:00,128
23:00:00,138
23:00:00,158
23:00:00,41
23:00:00,0
23:00:00,0
23:00:00,0

As seen in the screenshot, the start of the date/time(Thu Dec 12...) is legible in the x-axis, but only the hour actually needs to be seen, as the data is an hourly count. I'd like to make the hour the only x-axis text visible.

image
image1132×406 13.6 KB

Thank you,
--Phil

2

Googled "mssql format timestamp"...

3

Thanks. But i don't seem to be using it correctly, or it doesn't work in this case. Much like the convert command I tried in the initial post, adding a format command seems to return different data.

Really I think I need to know how to combine one of these commands (Convert, Format) with the existing query.

For reference, this is what I tried:

SELECT TOP (8)   FORMAT(t_stamp, 'hh:mm') t_stamp, CaseCount_L5Pack_Hourly  FROM HourlyCaseCount 
ORDER BY t_stamp desc

and this is the result:

12:00,161
12:00,120
12:00,0
12:00,126
12:00,144
12:00,139
12:00,163
12:00,141

Removing the Format command and leaving the rest of the query returns:

2024-12-12 12:00:00.000,175
2024-12-12 11:00:00.000,174
2024-12-12 10:00:00.007,167
2024-12-12 09:00:00.000,196
2024-12-12 08:00:00.007,166
2024-12-12 07:00:00.000,163
2024-12-12 06:00:00.000,159
2024-12-12 05:00:00.000,153

It seems like using t_stamp as an argument in the Format or Convert is a problem, and I don't know how to combine them with the query or to perform a secondary query to modify the result of the primary one.

4

I think you need to alias the column differently

SELECT TOP (8) 
    FORMAT(t_stamp, 'hh:mm') as formatted_time, 
    CaseCount_L5Pack_Hourly  
FROM 
    HourlyCaseCount 
ORDER BY 
    t_stamp desc
5

That looks promising. I'm going to test a little more before i use it. Thanks!

1 Like