Pulling datetime from SQL

Ignition
1

I am grabbing a table from SQL that has a datetime column. How do I get that time converted to a readable Date and Time?

image
image770×446 23.6 KB

2

So, one of Ignition’s own tag historian tables? Those store t_stamp as 64-bit UTC milliseconds. There’s a system function to convert that back.

3

This is just a table filled with the SQL data.

4

Not knowing how the database is constructed the below may work. What is the type of that Time Column in the DB ?

SELECT DATE_FORMAT(FROM_UNIXTIME(`Time`), '%Y-%m-%d %H:%i:%s') as "Date Time" FROM `MyDB`

Or just

DATE_FORMAT(Time, "%m-%d-%Y %H:%i:%s") AS 'Date', From 'My DB'
5

datetime.

Below is an example of the query and the tables in the SQL server.

SELECT ROW_NUMBER() OVER(ORDER BY Time ASC) AS IndexNum, PartNumber, ItemCode, JobNumber, Time, Quantity, ProductPrice, TotalPrice, UserName
FROM CHECKOUT
WHERE
    JobNumber LIKE :JobNumber
ORDER BY
    Time ASC

image
image707×263 9.21 KB

6

Ah, you need to configure your table’s column formats explicitly.

https://docs.inductiveautomation.com/display/DOC81/Table+Column+Configurations

1 Like
7

What format to do you want to display the Time in ? Try the below to tidy it up a little ? Or like Phil mentioned do it in the table.

SELECT ROW_NUMBER() OVER(ORDER BY Time ASC) AS IndexNum, PartNumber, ItemCode, JobNumber, DATE_FORMAT(Time, "%m-%d-%Y %H:%i:%s") AS 'Time', Quantity, ProductPrice, TotalPrice, UserName
FROM CHECKOUT
WHERE
    JobNumber LIKE :JobNumber
ORDER BY
    Time ASC
8

I cannot use the DATE_FORMAT because that is a MySQL only function.

9 
to_char(Time, 'dd-mm-yyy hh24:mi:ss') AS 'DATE TIME'
10

I got it figured out. Thank you everyone for your help and input!

11

Could you please share how you got it working?

12

Christopher, you just need to follow the solution in Post #5 marked as Solution.

In summary, in the table's PROPS,

  • set columns.x.field to the name of the SQL column containing the datetime value.
  • set columns.x.render : date.
  • set columns.x.dateFormat : HH:mm:ss or whatever dateformat you require.