How to format date column in SQL table?

Maker
1

Hi there,

I have an SQL query that summarize energy consumption (and some other values) for each hour for the last 24hours, and displays the value in a table.

image

I want to format the timestamp, to show 21:00-22:00 etc. How can I make this formating?

Any changes I make to this settings, does not have any effect on the format displayed....

image
image356×530 22.9 KB

This is the query i run:
image

2

You are converting your timestamp into a string, which the table is treating as a string. The dateFormat property does not affect string types. If you want to use the dateFormat property, then pass your timestamp in as a timestamp. (No conversion, no date format in SQL query)

I don't think you can get the format you want using the dateFormat property anyways. You might be able to get away with building the string for each row in the SQL query by doing something along the lines of:

concat(DATE_FORMAT(Timestamp, '%y.%m.%d %H:00')), ' - ' , DATE_PART('hour', Timestamp) + 1, ':00') AS DisplayTimestamp

But that may be computationally heavy.

1 Like
3

Thanks you! That might me a solution, but I was hoping there is a easier solution, if not, I will go with your suggestion.

I noticed that the formating worked on another query (which is almost the same, just sorting on each date instead of hour).

Maybe there is another way of doing the SQL query so that the timestamp will be formatable in ignition?

image

4

Formatting works on your second query here because you have your timestamp cast as a Date, which is a data type that the table accepts as something that it can apply the dateFormat configuration to.

If you want to be able to use date formatting on the timestamp then it needs to be a Date/Time data type. These are brought into ignition and converted to java.util.Date types, which allow the date formatting to be applied by the table.

You can also get away with bringing the value in as milliseconds since the epoch, since that is what is technically sent to the table component

5

Suggestion: change your column header to either "Hour starting at" or "Hour ending at" with a label over the table stating "Hourly energy consumption". Then you don't have to do any manipulation of your SQL but just set the table date column dateFormat : hh:00.

This will be easy to code and easy to read.
You should also be able to use the same dataset (without a second call to the database) for a chart of the data.

I'd be very nervous about your SQL formatting the date as %H:00:00. Any errors in the timing (say, a reading at 13:37) will be rounded down to "on the hour". You will be masking errors and making them difficult to debug.

6

Thanks again! I tried to make a query for this which wasn't too easy for me and my basic SQL knowledge, but it does work! Now i get to format the timstamp.

SELECT 
    FROM_UNIXTIME(subquery.timestamp_seconds) AS timestamp,
    SUM(Consumption) AS Consumption,
    SUM(Cost) AS Costs,
	sum(Subsidy) as Subsidy,
	sum(ActualCost) as ActualCost,
	avg(HourPrice) as AvgPrice
    
FROM (
    SELECT 
        UNIX_TIMESTAMP(DATE_FORMAT(Timestamp, '%Y-%m-%d %H:00:00')) AS timestamp_seconds,
        Consumption,
        Cost,
        Subsidy,
        ActualCost,
        HourPrice
        
    FROM 
        HetlelidAuto.Energy
    WHERE 
        Timestamp >= NOW() - INTERVAL 24 HOUR
) AS subquery
GROUP BY 
    subquery.timestamp_seconds
ORDER BY 
    timestamp;
7

Thats is of couse an option. But as mention above i got it to work some how. I will look into what happens if any write errors occurs, thanks for bringing that up :slight_smile:

8

OK, but you're leaving a bit of a mess for whoever comes after you.

Tip: Please see Wiki - how to post code on this forum. Post code - not pictures of code. Anyone who wanted to test or edit your code would have to type it all out again - or use OCR.

9

Sorry, i've edited my last post :slight_smile:

1 Like