Why is datetime column being converted to a number in a query?

When doing a select query of a column type “datetime” Ignition is converting the results to a number value by default.

Why is it doing this?

Without CAST()
image

With CAST( AS CHAR)
image

The number is a timestamp, thats the way the database stores a date.

You can let the table render it like a date that by adding the column here:
(And choose dateFormat of your choice)
image

(you dont need all the variables listed: this will work just as well)
image

2 Likes

Thank you, that is very useful info to have and it will definitely help.

Regard database results: (pardon my ignorance here) Are you saying that any program that returns a string date (YYYY-MM-DD HH:MM:SS) from a datetime field is doing a conversion? (ie, the commandline interface for MariaDB shows a date format, where Ignition shows a number)

image

It’s a Perspective thing. Javascript doesn’t have a datetime datatype. At bottom, Javascript has numbers and strings, so Perspective has to coerce all data into one or the other on the way to the browser. Perspective components that manipulate dates know to convert back and forth on the front end. As @victordcq pointed out, the Perspective table will also do this if you configure the column to render as a date.

Brilliant, that makes sense. Thanks!

Yes every database that uses a datetime field does conversions. Tho i might be wrong that its always timestamps, but some sort of conversion probably always happens, it stores timezones and stuff in those fields too sometimes.