# Timestamp issues regarding db insert

**URL:** <https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649>\
**Category:** General Discussion\
**Tags:** ignition79, database, scripting\
**Created:** [June 28, 2017, 6:14pm UTC](https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649 "2017-06-28T18:14:29Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![kevin.diaz](https://avatars.discourse-cdn.com/v4/letter/k/7993a0/32.png) [@kevin.diaz](https://forum.inductiveautomation.com/u/kevin.diaz)\
**Post date:** [June 28, 2017, 6:14pm UTC](https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649/1 "2017-06-28T18:14:29Z")

</div>

Hi!  
I've been struggling with the timestamp insert into my database.  
It's a Microsoft SQL Server using the MSSQL Translator.

**My main window display 3 buttons, Start, Pause and Stop.**  
I'm trying to create a table in the windows using also a date Range component to track wheter for some ID product the button start was pressed and button stop was pressed. So when the user specifies the range, a whole list of each product ID will be displayed showing details of its behaviour.

Using my DB connection I'm attempting to send an Int value to the DB, creating new rows for every time the Start button is pressed. When pressed, my database should create a record of the date when the button was used, and then will register when the STOP button is pressed.

My idea for the table structure was something like

- **id (int) not null**
- **StartDate (timestamp)**
- **Status (int)**
- **EndDate (timestamp)**

So, first issue. When creating the table, an error regarding the timestamp columns didn't allow me to create two columns of this type, therefore I've created two tables with these two dates one on each table. This way when START is pressed, this should insert the StartDate on table1 with the ID specified and when STOP is pressed, that should insert the endDate on table 2 with same ID.  
So when creating the main table, a simple join would allow me to see these whole information.

I've developed scripts for the behaviour of every button and attempted the following

> system.db.runPrepUpdate("INSERT INTO wo\_registry (wo\_id, start\_date,"+  
> " status) VALUES (?,?,?)", [wo\_number,now(0),value\_state])  
> **NameError: name 'now' is not defined**

this

> system.db.runPrepUpdate("INSERT INTO wo\_registry (wo\_id, start\_date,"+  
> " status) VALUES (?,?,?)", [wo\_number,CURRENT\_TIMESTAMP,value\_state])  
> **NameError: name 'CURRENT\_TIMESTAMP' is not defined**

and some other forum soultions like

> import datetime  
> now = datetime.date.today()

**java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO wo\_registry (wo\_id, start\_date, status) VALUES (?,?,?), [7811436, 2017-06-28, 1], , , false, false) Cannot insert an explicit value into a timestamp column.**  
**Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.** \<- I've tried this also too 😢

But nothing seems to work, I cannot insert the date when my button is pressed for the ID 😭 😭 😭 😭 😭 😭 😭 😭 😭 😭

Any kind of help will be appreciated. Am I copmplicating my existence? is there anyway of doing this simpler? Is there something like an Ignition feature that I don't know yet?

**I've tried inserting** row data without specifying the date, hoping the timestamp automaically will create that record, but had no success in the creation of this, trash data is inserted in that column. AND also when I first tried creating the table, this wouldn't allow me to define dafult values, like the **on update current\_timestamp** 😠

**Some random thoughts:** I'm almost sure that I don't require tags, because the data is for information purposes, and there are thousand of IDs, so doing something like historian wouldn't be useful, I guess 😕

Some Background: I'm fully new at Ignition, I've completed the Core Ignition Training modules, and watched all videotutorials of the basics. I've previously worked with Java using netbeans and using timestamp in my databases was far too easy when doing this kind of stuff.

Help 😢

---

<div class="post-metadata">

**Author:** ![Duffanator](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/duffanator/32/3560_2.png) [@Duffanator](https://forum.inductiveautomation.com/u/Duffanator)\
**Post date:** [June 28, 2017, 6:38pm UTC](https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649/2 "2017-06-28T18:38:41Z")

</div>

I don’t think you’re getting a proper datetime value to put in the database. Try this python code to get the current datetime value (there are other ways of doing this as well but this is what I use).

```auto
from java.util import Calendar
cal = Calendar.getInstance()
now = cal.getTime()

```

---

<div class="post-metadata">

**Author:** ![erfritts](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/erfritts/32/3660_2.png) [@erfritts](https://forum.inductiveautomation.com/u/erfritts)\
**Post date:** [June 28, 2017, 6:44pm UTC](https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649/3 "2017-06-28T18:44:10Z")

</div>

Hi Kevin. The Microsoft equivalent for MySQL’s now() function is getutcdate() or getdate().

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 28, 2017, 7:21pm UTC](https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649/4 "2017-06-28T19:21:28Z")

</div>

Hi Kevin,  
You are mixing a few non-scripting items into scripts where they don’t belong. The now() function you tried to use is an Ignition expression function, not a scripting function. You need to use something like Duffinator’s code to acquire an actual timestamp in jython (though I would grab an instance of java.util.Date directly instead of using java.util.Calendar).  
When now() didn’t work for you, you tried to insert SQL’s CURRENT\_TIMESTAMP built-in. That would have worked if placed directly within the SQL text, but it cannot be substitution item. Jython tries to find it as a variable name in jython, and that doesn’t exist.  
Try this:

```auto
from java.util import Date
system.db.runPrepUpdate("INSERT INTO wo_registry (wo_id, start_date, status) VALUES (?,?,?)",
	[wo_number,Date(),value_state])

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 28, 2017, 7:23pm UTC](https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649/5 "2017-06-28T19:23:24Z")

</div>

Or this:

```auto
system.db.runPrepUpdate("INSERT INTO wo_registry (wo_id, start_date, status) VALUES (?,CURRENT_TIMESTAMP,?)",
	[wo_number, value_state])

```

---

<div class="post-metadata">

**Author:** ![erfritts](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/erfritts/32/3660_2.png) [@erfritts](https://forum.inductiveautomation.com/u/erfritts)\
**Post date:** [June 28, 2017, 8:44pm UTC](https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649/6 "2017-06-28T20:44:17Z")

</div>

Your answer made me take a second look. There is an SQL function now() but it is better used with a Select statement. I simply did this when I used an Insert

tstamp = system.db.dateFormat(system.tag.read("[System]Client/System/CurrentDateTime").value, “yyyy-MM-dd HH:mm:ss”)  
system.db.runPrepUpdate(“INSERT INTO wo\_registry (wo\_id, start\_date, status) VALUES (?,?,?)”,  
[wo\_number,tstamp,value\_state])

But if I had to use a Select I would still use the native date time function for my DB.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [June 28, 2017, 8:45pm UTC](https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649/7 "2017-06-28T20:45:24Z")

</div>

> [@pturmel](#):
>
> Try this:
> 
> ```auto
> from java.util import Date
> system.db.runPrepUpdate("INSERT INTO wo_registry (wo_id, start_date, status) VALUES (?,?,?)",
> [wo_number,Date(),value_state])
> 
> ```

This can also be simplified even more using Ignition's built in `system.date` functions:

```python
system.db.runPrepUpdate("INSERT INTO wo_registry (wo_id, start_date, status) VALUES (?,?,?)", [wo_number,system.date.now(),value_state])

```

---

<div class="post-metadata">

**Author:** ![kevin.diaz](https://avatars.discourse-cdn.com/v4/letter/k/7993a0/32.png) [@kevin.diaz](https://forum.inductiveautomation.com/u/kevin.diaz)\
**Post date:** [June 28, 2017, 9:37pm UTC](https://forum.inductiveautomation.com/t/timestamp-issues-regarding-db-insert/14649/8 "2017-06-28T21:37:46Z")

</div>

Hello all. Thanks for the support.  
I tried all your suggestions and finally decided to change the timestamp value of my column to datetime _(alter drop and alter add)_, and use the **system.date.now()** provided by @paul-griffith

Thank you all for the fast response, it was really useful for determining what was the best suitable option for my project.