Dealing with Dates, Times, & Timestamps are never easy!

Ignition
, ,
1

I have need to compare a value from a Datetime field from SQL to the current time in a script.
Unfortunately the value returned from my Name Query for the datetime value is of type java.sql.timestamp. When I try using the value in a system.date function I get an error like “1st arg can’t be coerced to java.util.Date”.
When I mocked this up in the Script Console I did not get these errors. Once I put it in a Project Library Script and called that script from the Script Console, I started getting the errors.

So my questions are multi-faceted.

  1. I know there are scope differences between Script Console and the Project Script Library, but why would system date function be concerned about scope? Or, is there another reason this works in the console but not in the project script?

  2. How do I handle converting the java.sql.timestamp to a java.util.date, which is the data type the system date functions are expecting?

Is there something else I’m missing here that isn’t jumping out at me?

Thanks for the help!

2

java.sql.Timestamp is a direct subclass of java.util.Date - do you have exact reproduction steps where you can’t pass one, but can pass the other? The way Java’s type restrictions work, anything that accepts a Date should automatically accept a Timestamp.

3

This is a common issue, send it through system.date.fromMillis(system.date.toMillis(sqltimestamp)) and it should convert to java.util.date type.

4

You can also convert it in your named query. I had a similar issue and it was easiest for me to use this in my query (MYSQL). Sql does not have From_UnixTime() but there’s a way to do it.
From_UnixTime(sqlth_1_data.t_stamp / 1000)

5 
from java.util import Date
# Return a java.util.date from java.sql.Timestamp
date = Date(sqltimestamp.getTime())

@PGriffith, from the Javadocs:

Due to the differences between the `Timestamp` class and the `java.util.Date` class mentioned above,
it is recommended that code not view `Timestamp` values generically as an instance of `java.util.Date` . 
The inheritance relationship between `Timestamp` and `java.util.Date` really denotes 
implementation inheritance, and not type inheritance.
1 Like
6

I prefer java.sql.Timestamp over simple java.util.Date, as it can hold the microsecond timestamps from Logix processors. (:

7

Thanks All!
Your help was, well very HELPFUL! :smiley:

8

How did you convert them to java.sql.Timestamp ?