Hi!
I am saving date information in a MySQL database. I am saving the date and time in separate columns. When I query the table via ‘MySQL Workbench’, the data appears as it should…
However, when I query the data via Ignition there is a date added to the time column and a time added to the date column…
The named query that ignition is using…
SELECT *
FROM batch_events
WHERE batch_id = :batch_id
Any help would be appreciated! Thanks
All ‘date-like’ SQL types get cast to a java.util.Date object by the JDBC drivers. It looks like the MySQL driver passes through a number of milliseconds since Jan. 1, 1970 at midnight (also known as the UNIX epoch).
Since your ‘date_stamp’ column doesn’t have a time associated, the driver sends the moment in time of midnight on the specific day.
Your ‘time_stamp’ column sends the number of milliseconds in a particular time. Java (Ignition) just adds that number of milliseconds to Jan.1, 1970 and reports that moment in time.
Why are you separating your dates and your times?
Yeah, don't do this.
Thank you, that makes perfect sense now.
I was passing in the date/time to the SQL database via a formatted string, but when it’s extracted for use in the program of course it gets cast to a date object.
To answer your question I only set it up like that due to the request of the project manager. I’ve never (and would not) do that on my own as it really makes no sense. It adds complexity in several ways.
Looks like there will only be a single column for the timestamp, as it should be. Thanks again!
Highly recommend either using system.db.runPrepUpdate or making a named query so you can use dates directly (like say the date property of a DatePicker component) in your query and let Ignition do the tedious work of formatting correctly.
Well, not formatting at all. The whole point of ? parameters in Prep queries (and colon-delimited params in Named Queries) is to NOT perform string conversions. The data is delivered in the appropriate native binary object representation.
(And use MySQL's TIMESTAMP type instead of DATETIME, to make you immune to time zone problems.)
That is what I meant - you the programmer don’t do ANYTHING except pass the java.utile.Date object, let just Ignition handle putting it into the query properly for you. I don’t know what it’s doing under the hood I assumed it is doing some sort of formatting.