Sql Lite database table t_stamp format

Ignition
1

Hi All,
I m generating report by pulling data from sql database table.
t_stamp value showing long integer (epoch/ unix format).
How to convert it to data & time format?

2

you can use the scripting function system.date.fromMillis(t_stamp) and system.date.format(date,"YYYY-MM-dd HH:mm")
https://docs.inductiveautomation.com/display/DOC81/system.date

you have same method with expression too
https://docs.inductiveautomation.com/display/DOC81/Date+and+Time

1 Like
3

I don't use reports so I don't know if you can modify the query, but if you can you should convert directly there:

select from_unixtime(t_stamp / 1000) date from table

image

1 Like
4

Hi,
Thanks for reply.
I m getting error below
image

5

That's because it's sqlite. You'll need to find the equivalent in the doc.

https://sqlite.org/lang_datefunc.html

6

Don't use this until/unless you need to. Pass java.util.Date objects around. Convert to string for display only, and only if Ignition's components won't do it for you. (A component's native conversion will handle client time zones and locales for you.)

1 Like
7

You really should avoid SQLite for actual database connections. It is a single-threaded DB intended for very light duty (configuration file type tasks) and is missing many SQL features.

If you need something free, install PostgreSQL or MariaDB (or MySQL if you must).

8

yes i know, it's for that i prefer work with date object and not with str format.
But the question is

How to convert it to data & time format?

But thank tou for this precision :wink: