# Manipulate Timestamp

**URL:** <https://forum.inductiveautomation.com/t/manipulate-timestamp/70418>\
**Category:** Ignition\
**Created:** [February 28, 2023, 12:50pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418 "2023-02-28T12:50:14Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [February 28, 2023, 12:50pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/1 "2023-02-28T12:50:14Z")

</div>

I have a report that im sending out daily via transaction group and named query. The data that is reporting pulls at 6am and calculates water flow totals from 6pm-6am. When I put the data into a table the timestamp obviously pulls for the day that the query is scheduled to run, however the data from 6pm-6am is considered the previous day. Is there a way to have the timestamp show one day back with the same data it has now?

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/3/83d0f295e0a4a7bbd2d24b1ac1304d1614bb2d45.png)

So instead of Feb 28th it would show Feb 27th with the same data.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 28, 2023, 1:11pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/2 "2023-02-28T13:11:15Z")

</div>

Sure, add a parameter and use an expression to calculate the day before. Then use that parameter in the report.

```auto
addDays(now(0), -1)

```

---

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [February 28, 2023, 1:12pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/3 "2023-02-28T13:12:40Z")

</div>

This will only change the timestamp and not the data correct?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 28, 2023, 1:13pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/4 "2023-02-28T13:13:36Z")

</div>

Correct, parameter's don't do anything unless you use them in a query, script, or as a key.

---

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [February 28, 2023, 1:15pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/5 "2023-02-28T13:15:33Z")

</div>

Sorry for so many questions.....where specifically do I add the parameter to? Just create a new script in the report and create the parameter with that expression? Im new to this and still learning.

---

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [February 28, 2023, 1:16pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/6 "2023-02-28T13:16:34Z")

</div>

Or just add a new parameter where I have start date and end date?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 28, 2023, 1:20pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/7 "2023-02-28T13:20:40Z")

</div>

Correct, you could name it whatever you want, then just use that as the key where you want on the report.

---

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [February 28, 2023, 1:20pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/8 "2023-02-28T13:20:59Z")

</div>

So i kind of got it, however now it shows the same date for each day.  
 ![Report1](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/5/954d48cfaa90fe075d40b7a815384ad2513832f6.jpeg)

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 28, 2023, 1:22pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/9 "2023-02-28T13:22:14Z")

</div>

Oh, I see. I misunderstood what you were asking. How were those dates created previously? With a query?

---

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [February 28, 2023, 1:22pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/10 "2023-02-28T13:22:45Z")

</div>

Yes, so its just showing the timestamp for each query entry.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 28, 2023, 1:25pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/11 "2023-02-28T13:25:48Z")

</div>

Then do the date manipulation in the query itself, which may vary based on your DB flavor. For MySQL, it would look like this

```sql
select DATE_ADD(date, INTERVAL -1 DAY) as date , flow total 
from some_table
where ...

```

---

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [February 28, 2023, 5:44pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/12 "2023-02-28T17:44:45Z")

</div>

Im just using a Named Query.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 28, 2023, 6:01pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/13 "2023-02-28T18:01:52Z")

</div>

If you don't want to change the datasource from the named query, then you'll have to loop over the query results and update the dates in a script  
Assuming your data source key is named `flow_history`, add a script data source _ **after** _ the named query source.

```python
# Untested script manipulation
raw_ds = data['flow_history'].getCoreResults()
headers = ['Date','Flow Total']
dataOut = []
for row in system.dataset.toPyDataSet(raw_ds):
    newDate = system.date.addDays(system.date.parse(row['Date'], 'MMMM dd, yyyy'), -1)
    newRow = [newDate, row['Flow Total']]
    dataOut.append(newRow)
# updatedData is the data key to use in the report designer
data['updatedData'] = system.dataset.toDataset(headers,dataOut)

```

Honestly, just duplicate the named query with the date\_add and call it report version or something and skip the script.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 28, 2023, 6:11pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/14 "2023-02-28T18:11:16Z")

</div>

Do you know what database you are using? (MySql, MSSQL, Postgres?)

---

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [February 28, 2023, 6:14pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/15 "2023-02-28T18:14:29Z")

</div>

MSSQL database

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [February 28, 2023, 6:17pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/16 "2023-02-28T18:17:12Z")

</div>

looks like MSSQL's syntax for date add is

```sql
select DATEADD(day, -1, date) as date , flow total 
from some_table
where ...

```

[SQL Server DATEADD() Function (w3schools.com)](https://www.w3schools.com/sql/func_sqlserver_dateadd.asp)

---

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [March 1, 2023, 1:20pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/17 "2023-03-01T13:20:49Z")

</div>

So when you say skip the script and just add the date\_add, am I duplicating the named\_query in the report data sources or are you saying duplicate the entire named query im already using?

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 1, 2023, 1:29pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/18 "2023-03-01T13:29:27Z")

</div>

Easiest way would be to just write the new query _in the report_ as a SQL query data source. If you wanted to have it exposed as a named query and use it in other places, then just copy the original named query then modify it with the date addition stuff.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [March 1, 2023, 2:12pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/19 "2023-03-01T14:12:09Z")

</div>

Where are the dates in your screenshot coming from? What does the named query look like?

---

<div class="post-metadata">

**Author:** ![kaleb.s](https://avatars.discourse-cdn.com/v4/letter/k/f08c70/32.png) [@kaleb.s](https://forum.inductiveautomation.com/u/kaleb.s)\
**Post date:** [March 1, 2023, 2:28pm UTC](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418/21 "2023-03-01T14:28:15Z")

</div>

The dates are just coming from a TimeStamp which seems to be built into every query.

```auto
SELECT Flows_DataTotals.Cold
  Flows_DataTotals.Hot
  Flows_DataTotals.Total
  Flows_DataTotals.[TimeStamp],
FROM Flows_DataTotals
ORDER BY Flows_DataTotals.[TimeStamp] DESC

```

[Next page](https://forum.inductiveautomation.com/t/manipulate-timestamp/70418.md?page=2)