can't set a time field in DMI

[claudiobosticco]

SmartClient Version: SNAPSHOT_v13.1d_2024-11-05/Enterprise Deployment (built 2024-11-05)

Hello, I'v e got a SQLDataSource field declared as type="time", and I need to set its value for an add operation in a DMI. I've tried using java.time.LocalTime and java.sql.Time, but I get an error ORA-01861 because in the insert I see:

TO_DATE('2024-08-07 08:00:00','HH24:MI:SS')

database is Oracle 19

[Isomorphic]

If you just use a java.util.Date, the date portion will be ignored, and only the time used (in the server's timezone).

However, we'll check on those other time types - we'd like it to "just work".

[claudiobosticco]

In practice it's what I was already doing, I mean that I have in my dataSource:

<field name="START_TIME" type="datetime"/>
<field name="START_DATE" type="datetime" includeFrom="..."/>

and in my DMI I was simply doing

values.put("START_TIME", record.get("START_DATE"));
addRequest.setValues(values);

and then I was using a TImeItem on the client.

But then I tried to use time in the DataSourceField START_TIME, and in the INSERT I see:

TO_DATE('2024-08-07 08:00:00','HH24:MI:SS')

which raises the ORA-01861 error

Am I missing something obvious?

[Isomorphic]

What's the flow here? Does the DMI code you show form part of an update request before the database operation, so START_DATE comes up from the client and you are using it to populate START_TIME? Or is it part of a fetch request after the database operation, so START_DATE comes from the database and START_TIME is just manually populated from it, and the troublesome SQL is caused by a save of a DynamicForm containing a TimeItem?

[claudiobosticco]

It's entirely server side, I mean "record" (where START_DATE comes from) is from a dsRequest fetch constructed server side, and then it's used in the values of an add constructed again server side.

[Isomorphic]

Your DataSource snippet shows START_TIME declared as a "datetime", but earlier you state that it is a "time". Which of these is correct?

[claudiobosticco]

As I mentioned in post #3, I initially started with a datetime field and a TimeItem on the client side. Then, I decided to try setting the START_TIME field to type="time".

However, with type="time", I noticed that the TO_DATE function uses the 'HH24:MI' format. The issue is that I can't seem to find a way to set a valid value in my Java code, as it always ends up (in the TO_DATE function) being a datetime value in the yyyy-MM-dd HH:mm:ss format.

[Isomorphic]

We had difficulty reproducing this issue in either in our regular development environment or by directly using the same built package you are using. It turns out this is because the issue only arises when you use a different dbName to our default "Oracle". The underlying problem is a typo in our internal "framework.properties" file - a usage of "Oracle" where it should be "oracle" (note, all lower-case).

This issue is now fixed, so you can pick that up in tomorrow's builds. Alternatively, you can fix it in the build you already have by adding one of the following lines to your "server.properties" file:

# Either set the property at dbType level - ie, "oracle" rather than "Oracle"
sql.oracle.defaultTimeFormat: HH:mm:ss

# Or set it at dbName level, using whatever dbName you have chosen
sql.MyCustomDbName.defaultTimeFormat: HH:mm:ss

[claudiobosticco]

SmartClient Version: v13.1p_2024-11-14/Enterprise Deployment (built 2024-11-14)

Hello, now if I set the date like this:

values.put("START_TIME", record.get("START_DATE"));

the INSERT statement shows:

TO_DATE('13:00:00','HH24:MI:SS')

which appears correct. In the database column, however, I see 2024-11-01 13:00:00.000000. According to ChatGPT, this happens because Oracle adds a default date when only the time part is provided. I think I can live with that.

However, if I try to set a java.time.LocalTime, I encounter a validation error:

Validation error: [
    {
        START_TIME:{
            severity:"ERROR",
            errorMessage:"Must be a time."
        }
    }
]

Is this behavior expected?

[Isomorphic]

There are a number of things here. As our original response suggested, the value is expected to be an instance of java.util.Date. Our initial investigation centered around fixing the main issue - you weren't able to insert a time value at all. We do intend to add support for other Java time types, but that has not been done yet.

As far as Oracle adding its own date information in, unfortunately this is just the way Oracle behaves and nothing can be done about it if you want to use a native Oracle date column. SmartClient should ensure that only the time portion is returned to your application, so from that point of view it is just noise that you have to ignore. SmartClient also supports storing dates, times and datetimes in text or numeric columns = see https://smartclient.com/smartclient-...torageStrategy

[claudiobosticco]

ok, that's not a problem for me, as I said I can use it as is, let me know if you add support for types other than java.util.Date, so I can try it.
Thanks for now

[claudiobosticco]

SmartClient Version: v13.1p_2026-02-23/Enterprise Deployment (built 2026-02-23)

Hi, I’m noticing that when using a DataSourceField of type time in a SQLDataSource, when I edit the value from the client, in the database I see a value like 1970-01-01 00:00:13 (for a time of 13 seconds).

However, when I insert the value on the Java side in a DMI, if I simply use new Date(13 * 1000), I get one hour more (I’m in the CET time zone).

Instead, I have to do something like this:

value = Date.from(LocalDateTime.ofEpochSecond( 13, 0, ZoneOffset.UTC ).atZone(ZoneId.systemDefault()).toInstant());

I’d like to know whether this is expected behavior, whether there is a better best practice, or whether I should actually expect the framework to handle this situation automatically.

[claudiobosticco]

I need to clarify further: I get dates on January 1st, 1970 when I use a field type="datetime", whereas with type="time" I get today’s date (as already discussed earlier in the thread, using Oracle).
However, regarding the time portion, the behavior is the one described in the previous post.

[Isomorphic]

When you create a Date in Java in general, you're going to get it in the JVM's configured timezone, which is typically the timezone of the server. That behavior is JVM-based and not something SmartClient can influence.

As far as the browser, datetimes and times are displayed in the user's local time (or the configured display timezone), and transmitted to the server as UTC values to avoid ambiguity.

Then, as far as how dates are stored, you can control what TZ is used for storage via useUTCDateTimes in server.properties.

You didn't give enough information for us to explain what you're seeing (we would need client timezone, server timezone, DB timezone, JVM settings, server.properties settings, and how you constructed the date before switching to using UTC), but, if you want to work in UTC, yes in general you would need to construct Java dates with that timezone specified, and there's no way for SmartClient to auto-compensate.