Hi,
I'm struggling with a time(0) field in sql.
I have a field time(0) in sql. When I select this field from ignition, even when I select the field as "SELECT CAST(T1_INICIO AS time)" , the query returns: "Mon Jan 01 06:00:00 CET 1900"
I need to operate with this field, so I need to format that. When I do a "system.date.format" the return is a string, so I can't operate with that. For this reason I should do then "system.date.parse" to have a date again.
I don't want to use a datetime field in database because I won't use that info and because I need to compare this hour field to current hour. So I would need to update every day the field or transform current date into 01/01/1900 every time I need to operate with that.
I'm sure there is another way to do this, smoother and more clever. But I can't find solution.
Has anyone an idea to do that?
thanks!
If you format it, it's for display. Dates and time do not have an intrinsic format. They have a year value, a month value, a minutes value... etc.
A format defines how it appears on the screen.
It appears as "Mon Jan 01 06:00:00 CET 1900", because I don't think ignition as a 'time' type, and so it displays it as a datetime.
If you want to make sure you're comparing time only, you can use the 'dateExtract' or 'get*' expression functions. But I'd try doing the comparison first, make sure it actually doesn't behave like you want it to. It might turn out that you don't actually have an issue here.
Use a datetime (or datetime2 to have a timezone component) in the database and just not use the date part unless you need it. It never hurts to have more information available if required.
Use a time column as you are which will then be converted to a java.util.date object where you will need to extract the time parts that you care about.
Use an integer and just save the hour as an integer.
Personally I would strongly recommend that you take option 1. There is a small price to pay for using a date when you date when you don't need all of the parts right now, but if in the future you find that you do need it, it is much better to just stop ignoring it than to go back and change the table schema to add a date in.