How to stop Active Objects to automatically convert TIME field to TIMESTAMP?

[smhf97]

Hi everyone,

I defined a simple AO entity called TimePeriod that has two java.sql.Time fields, one for start of the period and one for the end.

When I create a TimePeriod and save it in java, it gets converted into TIMESTAMP in the database. The problem is that when I want to get a Time field of this timeperiod in java, a cast error raises: java.sql.Timestamp cannot be cast to java.sql.Time (I'm guessing this is because java thinks it should be Time but it's actually a Timestamp).

I tried to manually (with SQL) alter the column type and change it to TIME, but after that AO keeps raising this error: can't find type 92 (precision=6) in field END_TIME.

Is there anyway to fix this without having to change the field type in the entity definition?

Thanks!

[Nic Brough -Adaptavist-]

You should never be looking at the database, so you shouldn't be caring that active objects is doing this.  The databases expect to use Timestamps and trying to change that is not something you should be worrying about, let alone breaking it by changing the database.

A timestamp is not a duration as you're trying to store anyway, you're using the wrong type of field.

[smhf97]

Hi @Nic Brough -Adaptavist- Thank you for your answer,

I did not intend to directly manipulate or even look at the database, but the error got me there. I can't use the field getter function (like getEndTime, which returns a java.sql.Time) because upon using it, the cast error occur.

So then I went to see where is Timestamp coming from and I saw the column type was TIMSTAMP but I defined a Time field (which corresponds to TIME type in SQL).

I'm using two java.sql.Time fields to create a duration (as AO does not directly support the Duration type).

Why does even this change of Time type happen? It changes the type and then when I want to retrieve the value, it raise a cast error! (cause java expects sql.Time but it returns sql.Timestamp)

Thanks!

[Nic Brough -Adaptavist-]

Ok, that's good (not looking at the database).

The change here happens because AO does not have code for storing times or dates.  It just stores a date-time.  Those need dates on them, so if you are writing code to use just the time part of the data, your code will need to cope with the date part.  (Note that for date only fields, Jira makes an assumption that the time will be 00:00:00 - you could do that for dates too, assume epoch time, or just completely ignore the date part of it)

But I don't think it makes a lot of sense to use two time fields to represent a duration - it's duplication and, actually, the data part of the timestamp that AO is forcing makes sense.   Consider the case where you've got say start: 11:11 and end: 22:22 - whilst the obvious information in there is 11 hours and 11 minutes, it also says 25 hours and 11 minutes, and 49 hours and 11 minutes and so-on.

I'd store a duration as a simple number - you'd have to assume a unit, minutes, seconds, milliseconds, hours, etc, but you'd have one field and a consistent way to use it (possibly not even needing any casts - you can add numbers straight to a "start date" if it's a date-time field and get an end date-time without any casting if you're using the right unit for it)

[smhf97]

Thank you for your precious time!

Assume that I have only one Time field. Are there any ways to use Time type for my AO field?

Because in 'fieldtype-h2.xml' file I can see the java.sql.Time is a know and valid type for AO fields.

Thanks!

[Nic Brough -Adaptavist-]

Your use of the time field is broken, and you need to redesign so you can do it sensibly.

Active objects does not have a way to support your broken design, because it is broken.  You should move to storing time fields properly.

[smhf97]

It's not about the design. Even if I was to use your design, I should store at least one time field (for start of the duration) and you can't do that with AO as I explained in my question (my duration is in one day, so I just need the time part of the date).  

Just forget about my use case and assume you want to store one time field. Why can't you have a time field? It's in the supported AO field types.

Thanks!

[Nic Brough -Adaptavist-]

If the design is wrong, then it is about the design.  You are trying to represent a duration, and trying to jam it in to a time field (which isn't supported and isn't the right shape for your data) is the wrong thing to do.

You need to 

a) think about what you are trying to store and select a storage method that is appropriate for it

b) look at what the system supports and work with that.  (AO doesn't support your incorrect format, you need to move to using data it can support)

[smhf97]

You misunderstood my question. Imagine you want to have an AO entity with one time field. You can create this entity and insert into it, but the moment you want to get that time field, a cast error raises (regardless of your intention of using the time field. It has nothing to do with the design). 

[Nic Brough -Adaptavist-]

I am not sure that I did.  You're getting a cast error because the field is not to be used in the way you're trying to do it.

The solution to this problem is to not try to use the field like this.

[Kashev Dalmia _Soteri_]

@smhf97 I think what the actual issue you were having (separate and apart from whatever concerns one might have about your schema design) is that you're trying to use `java.sql.Date`, but ActiveObjects only supports `java.util.Date`.

See this ancient (but based on my experience, accurate) table of supported types: https://developer.atlassian.com/server/framework/atlassian-sdk/ao-0-18-x-upgrade-guide/