Convert a float to a datetime

Ignition
1

Good morning,

I have a float value (ex 5.444454).
How would I convert this float into CURRENT_TIMESTAMP + MM:SS?

Preferably I would like to accomplish this inside my script library. The trickiest part seems to be converting the float to MM:SS. Adding it to CURRENT_TIMESTAMP is just an afterthought.

Thanks for any help.

2

What date would 5.44454 represent to you? Just looking at it its not obvious. It sounds like you will definitely need a custom mapping function from float to timestamp.

3

So 5.44454 in this example would be MM:SS of 05:28.
So CURRENT_TIMESTAMP + 05:28 would be May 29 2020 08:59:28 (or close)

FYI this new DATETIME value will be inserted into a SQL database (the end goal)

4

Ah I see I think you mean then May 29 2020 08:05:28 since 5.44454 = 5:28 right?

Are you retrofitting data or is this for a new table? In MySQL doing CURRENT_TIMESTAMP() saves the minute and seconds by default so theres no need to do this. I think all databases have some timestamp-esque function that saves the current minute and second.

Otherwise, if you are retrofitting data, sounds like you will need an update function where you do something like

ALTER myTable
ADD COLUMN myNewDate datetime;

UPDATE myTable
SET myNewData = DATEFORMAT(some logic here)

That dateformat part will change depending on what database you use, and will give you the opporutnity to say I want the year to be the year dictated by this column, the month from this column, and the minute/seconds based on calculations on this column.

But again, if this is a new table, CURRENT_TIMESTAMP should already take care of minutes and seconds for you.

5

So my client is reading a FLOAT value from PLC. A tag change script is activated at some time and the FLOAT is converted to MM:SS and then added to whatever CURRENT_TIMESTAMP is.

Finally, this future timestamp is displayed on a screen for the user.

6

Oh ok so this is strictly for displaying values on screen form a plc? That makes it a bit easier.

You can do this to get the current date in an expression
dateFormat(NOW(), "yyyy-MM-dd") this will give you 2020-05-29, but theres ways you can make it say the month if you want, look at the documentation.

Then just concatenate this with an expression that does your calculation to convert your float from minutes and seconds. So overall

dateFormat(NOW(), "yyyy-MM-dd") +
# some logic here to convert PLC float to minutes and seconds

You may have to do a toStr() around both date format and your expression, but don’t quote me on that.

7

Since you’re asking to put it in a table, I’m assuming an insert. You didn’t mention which database, but this should work with most:

time = 5.444454

query = "INSERT INTO myTable (t_stamp, future_t_stamp) VALUES (CURRENT_TIMESTAMP,  CURRENT_TIMESTAMP + ( ? * INTERVAL '1 min')"

system.db.runPrepUpdate(query, [time])
2 Likes