Time format for SQL database


My clients are asking me to log start - stop - aborted times of sequences in ignition.

I want to run a script on a “tag change” in the gateway for now im testing it with scripting behind a button.

Everything seems to be working except i want to have it ordered by time. There are two ways to do this using the gateway time script or the CURRENT_TIMESTAMP in SQL query.

Here is my current update.
query = “INSERT INTO Logging (Nummer, Object_Name, Object_Type, Streng, Start_Time) VALUES (?,?,?,?,current_timestamp)”
args = [Nummer, Object_Name, Object_Type, Streng,]

The problem is that both of these use a wrong format so when I sort it doesnt sort correctly because it uses AM and PM.

I want the format YY-MM-DD-HH-MM-SS. Is it possible to reformat the DateTime tag from ignition to get this format or if any1 has another SQL command that makes the timestamp in this format that would be great!

Thanks in advance!

Some extra info when i read the datetime tag from ignition gateway i the following format

Mon Jun 25 15:53:23 CEST 2018

This doesn’t help the sorting either. I googled ignition but since I’m using ignition V7.7.2 I don’t think I’m able to use system.date.get().

Unfortunately since you are using 7.7 you won’t be able to use some of the convenient system.date functions but you can still use Java or Python libraries to parse the date however you’d like.

Also, from the looks of your code snippet, you have an extra comma after Streng in args.

Thanks for your reply thats what I was afraid for.

I’ve tried to convert the Datetime format using pything with the gateway tag but I have failed to format it in the correct order like I wanted to (YY-MM-DD-HH-MM-SS).

Can you point me in the right direction or have an example how to do this?

Also great eye I indeed had an extra comma there!

Your database isn’t formatting the date for you. Java is. Use the format property of the table customizer for where you are displaying the query results. Otherwise, use the dateformat expression function in bindings.

Hello every1 the reason I wanted this format was because of sorting from latest to newest.

Now I solved my problem by not sorting on the Date but I made an extra column “ID”. This column contains a number, now everytime I add data I will +1 this number and add it to the DB.

Databases sort datetime columns correctly regardless of formatting. Is the column in the DB not a datetime or timestamp?

The SQL DB columb was defined as a string varchar(max).

The data inserted is current_timestamp.

When I declared the columb as “timestamp” and tried to insert current_timestamp I recieved an error but can’t remember which one it was. Thats why I declared it as a string.

Formatting it in the table customizer didnt work either. Probably like you said because it isnt a datetime.

The SQL column type “Timestamp” means different things in different DB brands, unfortunately. It’s what you want in PostgreSQL. In SQL Server you want “DateTime”, usually.


I would use a Transaction Group. Create a tag to hold your process status. Create a separate trigger tag(make this one Read-only, don’t submit it to the DB). Use the tstamp capabilities of the Transaction Group.

On your tag change script you can update the Process Status Tag and then set your trigger tag.