# System.date.now() to sql t\_stamp

**URL:** <https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997>\
**Category:** Ignition\
**Tags:** ignition81, scripting\
**Created:** [November 16, 2023, 4:48pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997 "2023-11-16T16:48:46Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\
**Post date:** [November 16, 2023, 4:48pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/1 "2023-11-16T16:48:46Z")

</div>

I'm using the tag historian for tag history data. I've got a MySQL database set up and when using the database browser I see t\_stamps like so:

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/4/4417cdda780da96d6821c69b23a9d0642ff98a23.png)

I can't make heads or tails of these numbers. How can I format/transform `system.date.now()` to get a value like these t\_stamps?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 16, 2023, 4:51pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/2 "2023-11-16T16:51:31Z")

</div>

`system.date.toMillis(system.date.now())`

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [November 16, 2023, 4:52pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/3 "2023-11-16T16:52:38Z")

</div>

That's Unix time: number of seconds since 1970/01/01.  
I believe the column's type is responsible for the format it stores things in.

Use a datetime type to store your data instead if you don't want those timestamps.

---

<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:** [November 16, 2023, 4:55pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/4 "2023-11-16T16:55:27Z")

</div>

> [@pascal.fragnoud](#):
>
> That's Unix time: number of seconds since 1970/01/01.

Slight tweak - Java's default timestamp format is _milliseconds_ since epoch - so often functions that expect unix time will need you to divide by 1000.

---

<div class="post-metadata">

**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\
**Post date:** [November 16, 2023, 4:56pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/5 "2023-11-16T16:56:16Z")

</div>

So divide by 1000 here or no?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 16, 2023, 4:57pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/6 "2023-11-16T16:57:52Z")

</div>

The Ignition `system.date` functions and the Expression Language `toMillis` and `fromMillis` functions do this for you.

---

<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:** [November 16, 2023, 5:02pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/7 "2023-11-16T17:02:28Z")

</div>

If you're working within Ignition, you shouldn't need to.

If you're trying to make these human readable in your DB, e.g. MySQL's [`FROM_UNIXTIME`](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_from-unixtime), you'll need to divide by 1000.

---

<div class="post-metadata">

**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\
**Post date:** [November 16, 2023, 5:21pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/8 "2023-11-16T17:21:22Z")

</div>

So if I wanted to get the current time and go back 2 seconds before comparing against the database, should I subtract 2 or 2000? Or use `system.date.addSeconds()`?

---

<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:** [November 16, 2023, 5:24pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/9 "2023-11-16T17:24:16Z")

</div>

> [@amarks](#):
>
> system.date.addSeconds()

This. `system.date.now()` returns a Date object; `system.date.addSeconds` knows how to work with a Date object, and returns a modified one. Within Ignition, always use Date objects directly as much as you can - only format to string at the last possible moment for display purposes.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 16, 2023, 5:48pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/10 "2023-11-16T17:48:06Z")

</div>

> [@amarks](#):
>
> So if I wanted to get the current time and go back 2 seconds before comparing against the database, ...

You need to be careful with the historian database as the system uses analog or discrete deadband settings to reduce the database size and can use some interpolation on data retrieval.

Generally it's a bad idea to be querying the historian databases because it's so much work due to the data compression and the data partitioning which, by default, creates a new table for each month. Let Ignition do all the grunt work for you.

What's the real problem you're trying to solve here?

---

<div class="post-metadata">

**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\
**Post date:** [November 16, 2023, 5:54pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/11 "2023-11-16T17:54:59Z")

</div>

I have a BOOL tag that I'm monitoring for when it changes states from 0 to 1. When it does so, I need to record the timestamp - for the last 5 changes.

So, I've got an integer array (length 6) that gets written to using this tag's value changed script. It's length 6 because as part of the process I update the current index and clear the next before incrementing the index.

Then, these timestamps will be used to query the historical database for the 90 seconds after that timestamp.

---

<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:** [November 16, 2023, 6:14pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/12 "2023-11-16T18:14:55Z")

</div>

I recommend you do all your math with Ignition's date/time helpers, or directly with java functionality. The historian requires the start/end bounds to be `java.util.Date`.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 16, 2023, 6:48pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/13 "2023-11-16T18:48:03Z")

</div>

OK, your BOOL t\_stamp array seems to be working and may be the best approach.

For the history you could consider a script to loop through the BOOL t\_stamps and use system.tag.queryTagHistory() to retrieve the 90 s worth of tag history.

There are a large number of options in the function. I think you could cut it down to,

```auto
paths = [<list of tagPaths>]
startTStamp = <t_stamp from BOOL array>
results = system.tag.queryTagHistory(paths, 
        startDate = startTStamp, 
        rangeSeconds = 90,
        returnSize = -1,
        returnFormat = <probably 'Tall'>, 
        noInterpolation = <probably False>, 
        ignoreBadQuality = <you decide>
)

```

You might find it useful to use a table component to test this approach.

[https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory](https://docs.inductiveautomation.com/display/DOC81/system.tag.queryTagHistory)

---

<div class="post-metadata">

**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\
**Post date:** [November 16, 2023, 7:12pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/14 "2023-11-16T19:12:31Z")

</div>

that's the plan, but just to clarify the t\_stamps are not BOOLs (of course). There's one BOOL tag who's tag `valueChanged` script will write to an integer array tag when the value changes to 1.

Haven't gotten to the actual query part yet. I'll probably do this dynamically on the popup window so the query is only executed when needed.

---

<div class="post-metadata">

**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\
**Post date:** [November 16, 2023, 8:26pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/15 "2023-11-16T20:26:07Z")

</div>

So when I use

```auto
timestamp = system.date.toMillis(system.date.addSeconds(system.date.now(), timestamp_offset))

```

and try to put this into a list I get an "L" at the end. Why?

```auto
path = '[Area_01]Pumps_Motors/Test_Motor'

#get current time as millis for database and apply offset
timestamp = system.date.toMillis(system.date.addSeconds(system.date.now(), 0))

print timestamp

#read the index and start times array
index_num, times_arr, equip = [readQV.value for readQV in system.tag.readBlocking([path+'/Hist/Prev_Start_Times.Index',path+'/Hist/Prev_Start_Times',path+'Equip_Name'])]

index = int(index_num)
times = list(times_arr)

#update the current index with the new timestamp
times[index] = timestamp
print times

```

results in this output

```auto
1700166462224
[0, 1700166462224L, 0, 0, 0, 0]

```

Are these t\_stamps INTs or Strings?

---

<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:** [November 16, 2023, 8:38pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/16 "2023-11-16T20:38:02Z")

</div>

You are printing things. Printing yields an approximation of the actual object content. The `L` suffix is jython's notation for a long integer, as opposed to a simple 32-bit integer.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 16, 2023, 8:42pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/17 "2023-11-16T20:42:41Z")

</div>

... and you'll sometimes see Unicode strings represented by u'abcde'.

Yes, the t\_stamps are INTs (number of milliseconds since 1970-01-01.

---

<div class="post-metadata">

**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\
**Post date:** [November 16, 2023, 8:46pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/18 "2023-11-16T20:46:06Z")

</div>

Then I guess I need a long array instead of an integer array.

---

<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:** [November 16, 2023, 9:25pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/19 "2023-11-16T21:25:50Z")

</div>

~~Not until 2038:~~

> **[Year 2038 problem](https://en.wikipedia.org/wiki/Year_2038_problem)**
>
> The year 2038 problem (also known as Y2038, Y2K38, Y2K38 superbug or the Epochalypse) is a time formatting bug in computer systems with representing times after 03:14:07 UTC on 19 January 2038.
> The problem exists in systems which measure Unix time – the number of seconds elapsed since the Unix epoch (00:00:00 UTC on 1 January 1970) – and store it in a signed 32-bit integer. The data type is only capable of representing integers between −(231) and 231 − 1, meaning the latest time that can be prop...

~~A Python 'long' that is less than 32-bit-signed-int-max (231-1, 2,147,483,647) can and will be losslessly converted into an integer by Ignition when it's put into your int array.~~

---

<div class="post-metadata">

**Author:** ![amarks](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/amarks/32/45610_2.png) [@amarks](https://forum.inductiveautomation.com/u/amarks)\
**Post date:** [November 16, 2023, 9:27pm UTC](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997/20 "2023-11-16T21:27:20Z")

</div>

I tried to put it into an Integer Array tag and it didn't work. Worked once I changed it to a Long Array.

[Next page](https://forum.inductiveautomation.com/t/system-date-now-to-sql-t-stamp/81997.md?page=2)