Best practice question - writing current timestamp to SQL database

Ignition
1

Wondering if there is any significant distinction between the following methods of recording a timestamp while creating a database record:

  • Using Ignition's system.date.now() function, then inserting that value
  • Using the database's CURRENT_TIMESTAMP function (assuming Microsoft SQL Server)

Example of first one:

partNum = '555AB'
now = system.date.now()
query = """INSERT INTO Orders
(PartNum,InsertedOn) VALUES(?,?)"""
args = [partNum,now]
system.db.runPrepUpdate(query,args,'MyDbConnection')

Example of second one:

partNum = '555AB'
query = """INSERT INTO Orders
(PartNum,InsertedOn) VALUES(?,CURRENT_TIMESTAMP)"""
args = [partNum]
system.db.runPrepUpdate(query,args,'MyDbConnection')

Is it even worth splitting hairs over?

2

I usually just let the db do the creation date.
I would personally go with option two.

I would also recommend instead of writing raw queries create all your queries inside of named queries. It's a better practice and it will help you/anyone else behind you who may need to edit this application down the road. I've seen many projects where I have to dig and dig to find out that the queries are all raw queries while named queries you know where they are and what they are.

1 Like
3

CURRENT_TIMESTAMP is from the database machine's clock. system.date.now() is from the Ignition machine's clock. Which one is right for your application?

1 Like