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

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!