Data type Time is mostly useless
Description
Environment
Activity
Nicolas Micoud October 15, 2022 at 7:12 AMEdited
Hi @Carlos Ruiz
Since I migrated my v9 to recent sources (b23ea76) from 2022-09-02 which has https://github.com/idempiere/idempiere/commit/e56c1694647896b484aa875798fe9193e3165e5f, I notice a strange bug on a dedicated form which has a WTimeEditor using addValueChangeListener to detect change.
When a user write an hour for the first time, if the value is 01:00 (ie 1 hour), the change is not detected.
I've tested to revert changes from WTimeEditor.onEvent(Event) and it works like before.
I've also tested adding 1 millisecond to the calendar and it also works like before but I guess is not a nice way to handle this.
nb: I have another form with another WTimeEditor but is not impacted as it is using EventListener.
I'm not sure about the best way to deal with that (maybe is just to move my form from using ValueChangeListener to EventListener). But I would prefer to report this as it may affect other installations.
To test, you can find https://github.com/idempiere/idempiere/pull/1529 with 2Pack (it just create a simple form with 2 WTimeEditor).
Right after opening it, if you write 01:00 in the "ValueChange" editor, you'll notice nothing in the log.
Whereas if you open it and write 02:00 in the same editor, the change is detected.
You'll see that change is always detected from the Event editor.
wdyt?
Heng Sin Low August 29, 2022 at 2:54 AM
PostgreSQL has Time data type but unfortunately, no equivalent of that in Oracle. I think the convention here is to set the date part to 1970-01-01, that’s what the JDBC Time class do.
Recently I had the need to use a Time data type in an application.
From my understanding the intention of this field would be to save in the database just the time part of a datetime, iDempiere uses in database the same timestamp/date type for Date / Time / Date+Time
The problem is that the Time datatype is saving the date part with today’s date, and because of that is basically useless for being used later as a filter in a report or a process, or to compare two Time datatypes to see if one time happens before another time.
A simple test case in GardenWorld:
Login with role GardenWorld Admin
Open Resource Type window
Try to find a record with Time = 09:00:00 a.m.
Note, this is the Consultant resource type
The query says No Records found
This is because the Time Editor returns the time 09:00:00 with today’s date, and the record is saved in the database with date 1970-01-01
Furthermore, if you create a new record in Resource Type and select a time slot for example from 08:00:00 to 08:30:00 - then the time will be saved with today’s date, and you cannot use SQL to compare if the record with 08:30:00 is littler than 09:00:00
Same problem if you use a Time as a parameter in a process or report, it doesn’t help to compare against records in the database.
Possible solution: Assign always a fixed date part for the Time datatype, from what I see in the database, perhaps the original intention was to use 1970-01-01 for these kind of records (or maybe it worked like that before and it was messed up at some point of time)
If we got his way I think a migration script would be necessary to change the date part correctly for existing time fields.