# How to format date column in SQL table? **URL:** \ **Category:** Maker\ **Created:** [April 8, 2024, 7:48pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540 "2024-04-08T19:48:25Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [April 8, 2024, 7:48pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540/1 "2024-04-08T19:48:25Z")
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/a/1adc6000e527a1ecbd7cc0ea996b7d52fc52a834.png) 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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/e/0ef3ef1d9eabf571ea7584e316009169fc11b8ec.png) This is the query i run: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/5/f50ea540eaf31d3af49540365f3a80634b3ee733.png) ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [April 8, 2024, 8:16pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540/2 "2024-04-08T20:16:01Z")
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: ```auto concat(DATE_FORMAT(Timestamp, '%y.%m.%d %H:00')), ' - ' , DATE_PART('hour', Timestamp) + 1, ':00') AS DisplayTimestamp ``` But that may be computationally heavy. ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [April 8, 2024, 8:28pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540/3 "2024-04-08T20:28:59Z")
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/e/6ec07a91fc791700df8dbfa6c14d7950b6c85ead.png) ---
**Author:** ![ryan.white](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ryan.white/32/73193_2.png) [@ryan.white](https://forum.inductiveautomation.com/u/ryan.white)\ **Post date:** [April 8, 2024, 8:41pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540/4 "2024-04-08T20:41:27Z")
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`](https://www.postgresql.org/docs/current/datatype-datetime.html) 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 ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 8, 2024, 9:21pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540/5 "2024-04-08T21:21:44Z")
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. ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [April 8, 2024, 9:34pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540/6 "2024-04-08T21:34:31Z")
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. ```auto 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; ``` ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [April 8, 2024, 9:36pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540/7 "2024-04-08T21:36:09Z")
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 🙂 ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 8, 2024, 9:39pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540/8 "2024-04-08T21:39:21Z")
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](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831). 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. ---
**Author:** ![Vidar\_Hetlelid](https://avatars.discourse-cdn.com/v4/letter/v/dfb087/32.png) [@Vidar\_Hetlelid](https://forum.inductiveautomation.com/u/Vidar_Hetlelid)\ **Post date:** [April 8, 2024, 9:41pm UTC](https://forum.inductiveautomation.com/t/how-to-format-date-column-in-sql-table/88540/9 "2024-04-08T21:41:05Z")
Sorry, i've edited my last post 🙂