Convert long into a timestamp

Ignition
1

Hi,

I'm trying to create a dynamic chart. I've got series of data in a database (for example pH, with first column timestamp and second column the value) and I'm trying to display series into a chart. The series have different time record for their values, so I'm trying to subtract the first timestamp of a serie to all timestamps of the same serie so that all the series should have the same time range so they will be superimposed on the chart.
I can do this by converting the timestamp in a long and then I can easily do my subtraction. But I have troubles to convert back the long into a timestamp and so display the modified serie in a chart. I tried many things but didn't work so I need help. I'm using the function getTime() to convert in a long, but I don't know why, the function Timestamp() doesn't work.
Maybe there is also a better way to subtract timestamp, I tried to find one but I didn't.

Here's part of my code if that can help to understand:

    import java.sql.Timestamp	

    dataset = system.db.runNamedQuery("Charts/ph", {"ProductionLot":"3212016"})
	pydataset = system.dataset.toPyDataSet(dataset)
	first_value = pydataset[0][0]
	first_timestamp = first_value.getTime()
	#tot = system.date.fromMillis(first_timestamp) 
	#Timestamp function: parameter = time - milliseconds since January 1, 1970, 00:00:00 GMT --> 18000000
	#aaa = java.sql.Timestamp(tot)

In this code there are some of my tries to convert a timestamp into a long and then convert it back to a timestamp.

Thanks!

2

You don't need to import a library. Ignition has its system.date functions which should do all you need.

I'm not sure how the output is supposed to look but if you want the difference in seconds from the first reading you would use something like this.

3

Here's what I'm trying to do:
If I plot all the series in my chart without modify the timestamp I will have something like this:

image
image1482×362 8.89 KB

Instead, I want to subtract the first timestamp to all timestamp of each series to have something like this:

image
image1215×382 16.3 KB

I need the timestamp because records of data are sometimes made at different times, they are not regular (for example: for the first serie I record one value at 6pm then 7pm then 9pm, and for a second serie I record one value at 6:30pm then 7pm then 8pm then 9pm.) but I don't care that the date displayed on the X-axis is the wrong one.

So this is for that I'm trying to subtract the first timestamp to each timestamp of a column of a dataset. For now I success to subtract but I'm not able to convert the value into a timestamp again, and I didn't find a function for that

4

Q1. Have you a separate binding for each trend? If not, what is the structure of the data returned by the named query?
Q2. You want the X-axis in HH:mm:ss since the start of the test?

5

Data are recorded in a database and are all linked to a key value. This key value changes which allows to differentiate each series. My named query retrieves values with this key value as parameter and returns a dataset of two columns:

  • first column is the timestamp

  • second column is the value

So something like this:
image

The X-axis is not useful there, what interests me is to be able to compare the different series thanks to their trend, but for that I need them to be side by side, which is not the case if I don't modify them.

6

To specify how to retrieve my values for the trends:
I have a list of all the key values available (so all series), the user can select one then use a add button which will add this key value to an array. Then, with an update button, a script will make a loop depending on the number of key values added to the array. For each pass in the loop, a dataset will be filled with the data of the named query with the key value in parameter, then it will be modified and then added to the chart.

7

OK. Then I suggest you

  • Record t0, the first timestamp of the first series.
  • When each series is added then loop through the data and calculate the dateDiff in milliseconds and convert this to epoch time:
    t0 = pydataset[0][0]
    output = []
    for row in pydataset:
        msFromT0 = system.date.dateDiff(t0, row['t_stamp'], 'ms') 
        epochDate = system.date.fromMillis(msFromT0)
        output.append( [ epochDate, row['Bench_pH'] ] )

If you format your X-axis as HH:mm:ss it should look right.

8

Thanks it makes sense, but unfortunately functions are not working.
The dateDiff takes "Date" type as parameters but I have timestamp in my dataset so I think it's not working because of that. I tried several things to convert timestamp as Date type but I didn't success, any idea ?

9

java.sql.Timestamp is a subclass of java.util.Date, and should work fine in those functions (just losing fractions of milliseconds in conversion).

Please show your code and explain what "not working" means to you.

10

Yes I found this function and already tried it, but didn't work. Actually I don't understand why I can make the function getTime work but not the Timestamp function while they are from the same constructor.

When I say it doesn't work, it's because just after the function I put a command like:

self.getSibling("Label_info3").props.text = str(type(msFromT0))

with msFromT0 the result of the function, but the program don't go further than the line of the function. Even when I tried to display something else in my label, like "a", I have nothing.

This is my code (for now, I'm just trying to apply it for one value, I guess as soon as I am able to do it for one value, I can do it for the whole dataset):

	dataset = system.db.runNamedQuery("Charts/ph", {"ProductionLot":"3212016"})
	pydataset = system.dataset.toPyDataSet(dataset)
	first_value = pydataset[0][0]
	second_value = pydataset[1][0]
	self.getSibling("Label_info2").props.text = first_value
	self.getSibling("Label_info3").props.text = second_value 
	msFromT0 = dateDiff(first_value, second_value, "ms") 
	self.getSibling("Label_info2").props.text = str(type(msFromT0))
	epochDate = system.date.fromMillis(msFromT0)
	self.getSibling("Label_info3").props.text = str(type(epochDate))

My two labels "Label_info2" and "Label_info3" display the first_value and the second_value.

11

Because else, the easiest I think was to do:

	dataset = system.db.runNamedQuery("Charts/ph", {"ProductionLot":"3212016"})
	pydataset = system.dataset.toPyDataSet(dataset)
	first_value = getTime(pydataset[0][0])
	second_value = getTime(pydataset[1][0])
	sub = second_value - first_value 
    timestamp = Timestamp(sub)

but that didn't work, even if I put "import java.sql.Timestamp" at the beginning of my code

12

Ah, there's no jython function getTime(). But java.util.Date and java.sql.Timestamp both have a .getTime() instance method that yields long milliseconds. You can shorten that to its NetBeans property equivalent, .time. So,

	first_value = pydataset[0][0].time
13

But I don't have trouble with the getTime function.
I can:

  • get my first and second timestamps

  • convert them as long with the getTime function (I just tried your .time it's working too)

  • do my subtraction of second - first as they are long

But then I'm not able to convert back the result (which is a long) in a timestamp type

14

There's something you're not showing us, there's no way getTime works as is without it being defined first.

What's the exact output of type(pydataset[0][0]) ?
I think you really should be trying to make dateDiff work.

15

It's not in my interest to not showing you something...

Here's my whole code (linked to an event onActionPerformed of a button), I also put you values and type in comments:

def runAction(self, event):
	"""
	Method that will run whenever the selected event fires.

	Arguments:
		self: A reference to the component that is invoking this function.
		event: An object that holds information about the selected event type
	"""
	# Retrieve the selected values from the Dropdown boxe
	data_to_display = self.getSibling("Dropdown_data_to_display").props.value

	dataset = system.db.runNamedQuery("Charts/" + data_to_display, {"ProductionLot":"3212016"})
	pydataset = system.dataset.toPyDataSet(dataset)
	timestamp = pydataset[0][0]
	#timestamp = 1687178960070
	#type(timestamp) = <type 'java.sql.Timestamp'>
	first_value = pydataset[0][0].getTime()
	#first_value = 1687178960070
	#type(first_value) = <type 'long'>
	second_value = pydataset[1][0].getTime()
	#second_value = 1687182947547
	#type(second_value) = <type 'long'>
	sub = second_value - first_value
	#sub = 3987477
	#type(sub) = <type 'long'>
16

I tried to make dateDiff work, but I was not able to convert a timestamp into a Date type, as this function takes in parameters Date type

17

Okay, this is NOT what you showed us earlier, where you were using getTime(date) instead of date.getTime().

I'll check what can be done with those timestamp.

18

Oh yes you're right my bad for that. I tried and deleted a lot of things so I put what I remembered. But in my first message I wrote it like that
Thanks for you help

19

I can't seem to find an issue with this part:

from java.sql import Timestamp

s = Timestamp(1687178960070)
e = Timestamp(1687182947547)

delta = Timestamp(e.time - s.time)

This... just work. I can convert back to a timestamp without any issue, using the same values you gave us.

1 Like
20

I've seen some queries return sql.Timestamp or util.Date the way to solve this is to convert all to Date like

from java.sql import Timestamp

dataset = system.db.runNamedQuery("Charts/ph", {"ProductionLot":"3212016"})
first_timestamp = dataset.getValueAt(0,0)
if isinstance(first_timestamp,Timestamp):
    first_timestamp = system.date.fromMillis(first_timestamp.getTime())
#keep conding