Tim


>Thanks Roy. She was the last, it's just me and my brother now, so it's going to take some time to get used to...

That I can understand, and its generally the little things that sneak up and get you. Things that you're used to doing and suddenly you're not.

Roy

Rolf,

<< Ok but it is a little bit more complicated. So I will write what I realy want to do.

I have x Days (INTEGER) and also a Time (TIME) value. I want now combine the two to get an interval Day to MSeconds. My Time may be "01:35:10.456" and the days value is 5. Now I want get an interval from that like this "5 01:35:10.456". I have still not found a solution to do this yet.
>>

Okay, I finally got a chance to look at this, and here is the main problem: you're trying to use TIME values as intervals in your database(s), and that's the wrong way of doing things.  If you have a TIME value that you're using as an *elapsed* time, then you should make sure that it is defined in the database as an INTERVAL, not as a TIME.  Once you start trying to mix *absolute* TIME values with INTERVAL values, you're in for a world of hurt because you'll be constantly trying to convert between two completely different realms.  It's not that you can't do it, it's just that, as you found out, you're going to be doing a lot of CASTing.

For example, if you have your time values expressed as HOUR TO MSECOND intervals in your database, then the above is as simple as:

SELECT CAST(5 AS INTERVAL DAY) + INTERVAL '01:35:10.456' HOUR TO MSECOND

Of course, you'll only need the first CAST for the integer value, and the second value can come directly from the database table.  If you still need to use a TIME value, then you'll need to do another CAST:

SELECT CAST(5 AS INTERVAL DAY) + (MyTimeColumn - CAST('00:00:00.000' AS TIME))
FROM MyTable

Notice that you don't need to CAST the result of the subtraction.  By default, when subtracting a time from a time, the result is always an HOUR TO MSECOND interval value:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Arithmetic_Operators

(under "Date, Time, and Timestamp Subtraction").

However, there's one problem with all of this: there's a bug in ElevateDB's determination of the sub-type for the first SQL statement above, and it gets the interval type wrong.  So, I'll have to get a new build out that fixes this issue.

Tim Young
Elevate Software
www.elevatesoft.com

Rolf,

<< Tim, is this the right way to do this or is there still something better? >>

Sorry to answer a question with a question, but why are you expressing day-time intervals as fractional values in the first place ?  ElevateDB provides the facilities you need to work with intervals in either SQL or Delphi code:

https://www.elevatesoft.com/manual?action=viewmethod&id=edb2&product=rsdelphiwin32&version=10T&comp=TEDBEngine&method=SQLStrToDayTimeInterval

Day-time intervals are represented internally in EDB as Int64 values, so you can simply add and subtract them as necessary, and EDB can always convert them to whatever interval representation that you would like.

Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< That I can understand, and its generally the little things that sneak up and get you. Things that you're used to doing and suddenly you're not. >>

Yeah, it kind of just hits you every now and then...

Tim Young
Elevate Software
www.elevatesoft.com

Tim

Thanks for your insights.

Will AVG, STDDEV, MAX, MIN and SUM work with INTERVALS DAY TO MSECOND? Support for a TIME value for all this functions was the reason I did write this functions. So it was just simple to do a SUM(TimeToFraction(Duration)), which will not work with a TIME Field.

I agree now, that the datatype should be INTERVAL DAY TO MSECOND instead of TIME. I have learned realy much the last few days about INTERVALS.

Tim,

One big issue I still have is that the "period" part of an INTERVAL must be a constant. In most cases it will be another field or a variable.

So this SQL will not work (NewDays may come from an other table per join or another field in the same table):
UPDATE MyTable SET Duration = Duration + INTERVAL NewDays DAY;

How can this be done with a single SQL Stamtement without to write a script?

Rolf

Rolf,

<< Will AVG, STDDEV, MAX, MIN and SUM work with INTERVALS DAY TO MSECOND? >>

Yes.  It does all of the heavy lifting for you, leaving you free to convert the day-time interval into whatever representation that you wish (DAY TO MSECOND, DAY TO MINUTE, etc.).

<< I agree now, that the datatype should be INTERVAL DAY TO MSECOND instead of TIME. I have learned realy much the last few days about INTERVALS. >>

They really are a lot easier to deal with than trying to constantly perform the conversions manually.  I should know, because I spent a *lot* of time writing the conversions and it took a while to get them all 100% correct.

You're going to run into one problem in the EDB Manager, though: the EDB Manager isn't handling the input of the interval columns properly and won't allow you to enter in ':', '.', or ' ' in the table/query grids.  I've fixed this, though, and it will be in the next build.

Tim Young
Elevate Software
www.elevatesoft.com

Rolf,

<< One big issue I still have is that the "period" part of an INTERVAL must be a constant. In most cases it will be another field or a variable. >>

So this SQL will not work (NewDays may come from an other table per join or another field in the same table):
UPDATE MyTable SET Duration = Duration + INTERVAL NewDays DAY; >>

If you're just using another column in the same table (or a different table), then the update is easy if the other column is an integer (just cast it to a DAY interval) or already a day-time interval (just use it as-is).

Integer:

UPDATE MyTable SET Duration = Duration + CAST(NewDays AS INTERVAL DAY)

Day-Time Interval:

UPDATE MyTable SET Duration = Duration + NewDays

Tim Young
Elevate Software
www.elevatesoft.com

Tim

STDDEV doesn't work with an INTERVAL. Is this as expected or is it an bug? IMO this seems to be a bug,

Regards
Rolf

Rolf,

<< STDDEV doesn't work with an INTERVAL. Is this as expected or is it an bug? >>

It's a bug. I'll include a fix in the next build.

Tim Young
Elevate Software
www.elevatesoft.com