Hi,

I was wondering if anyone can tell me how to extract the milliseconds from a
TimeStamp field via SQL in DBISam 4?

If I subtract one timestamp from another the results are in ms, but I can't
figure out how to get the total milliseconds in general (ie, not just an
extraction, but the total milliseconds adding from midnight to that time).

Thanks & Regards

Adam.

Adam,

<< I was wondering if anyone can tell me how to extract the milliseconds
from a TimeStamp field via SQL in DBISam 4?

If I subtract one timestamp from another the results are in ms, but I can't
figure out how to get the total milliseconds in general (ie, not just an
extraction, but the total milliseconds adding from midnight to that time).
>>

You can use the *FROMMSECS functions for that purpose:

http://www.elevatesoft.com/scripts/manual.dll?action=mantopic&id=dbisam4&product=d&version=7&category=3&topic=8

they're at the bottom of that topic.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for your reply...

> You can use the *FROMMSECS functions for that purpose:
>
> http://www.elevatesoft.com/scripts/manual.dll?action=mantopic&id=dbisam4&product=d&version=7&category=3&topic=8
>
> they're at the bottom of that topic.

When I try the following:

select starttime, endtime, MSECSFROMMSECS(starttime)
from records

I get the error:

DBISAM Engine Error # 11949 SQL parsing error - Expected NULL, SmallInt,
Word, AutoInc, Integer, LargeInt, Currenty, Float, or BCD expression but
instead found starttime in SELECT SQL statement....


StartTime is at "TIME" field.

In addition to this, the manual states "Takes milliseconds and returns the
number of milliseconds (as a remainder of the above years, days, hours,
minutes, and seconds, not as an absolute)."

As such, won't it simply return the milliseconds within the time since the
previous 'Second' time, instead of the milliseconds since Midnight?

Thanks & Regards

Adam.

Adam,

<< When I try the following:

select starttime, endtime, MSECSFROMMSECS(starttime)
from records

I get the error: >>

You can't use those functions directly on a time.  They're for use on the
resulting milliseconds (numeric value) that result from subtracting two time
values.

<< As such, won't it simply return the milliseconds within the time since
the previous 'Second' time, instead of the milliseconds since Midnight? >>

If you want to just extract the milliseconds from a time field, use the
EXTRACT function.  See the same Functions area of the manual for the syntax.

My apologies for the confusion - I was under the impression that you wanted
to get the elapsed time information for two times.

--
Tim Young
Elevate Software
www.elevatesoft.com

Good morning Tim,

Thanks again for your reply.

> If you want to just extract the milliseconds from a time field, use the
> EXTRACT function.  See the same Functions area of the manual for the
> syntax.

Unfortuantly this too will not do what we're after. This extracts the
milliseconds from the last whole minute.

If the time is 08:15, I'm of the understanding that the stored value for the
Time field in the database would be 2970000 (as the stored value is in
milliseconds from midnight).

What I'm trying to do is retrieve that actual value. (Either that, or I'm
happy to return the number of seconds or even minutes of the time since
midnight, so either 495 minutes or 29,700 seconds would also be adequate as
a result.

The EXTRACT function does not do this, in that an extract function on 08:15
would return:

0 milliseconds
0 seconds
15 minutes
8 hours

I can see that if I use something like the following:

select starttime, endtime, (((extract(hour from endtime) * 60 + extract
(minute from endtime)) * 60 ) + extract(second from endtime)* 100 as
EndMilliseconds
from records

I can get the information that I'm after - but I thought that if the data
was stored in milliseconds to start with, that their should be an easier way
to get the information direct.

Is there a way to grab the actual value that is stored from the table via
SQL natively?

Thanks & Regards

Adam.

Adam H. wrote:

>
> If the time is 08:15, I'm of the understanding that the stored value
> for the Time field in the database would be 2970000 (as the stored
> value is in milliseconds from midnight).

Adam

Just so you don't need to try it, CAST doesn't work (in v3.30 anyway)

SELECT CURRENT_TIMESTAMP AS TimeStamp ,
      CAST(CURRENT_TIMESTAMP * 1000 AS INTEGER) AS MilliSecs
FROM MyTable TOP 1

I had thought that I was going to solve your problem with a flash of
brilliance but ...

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Hi Jeff,

Long time no talk! How's it going over there across the Pacific?

> Just so you don't need to try it, CAST doesn't work (in v3.30 anyway)

Thanks for the info. Doesn't work in v4 either. (Tried it

> I had thought that I was going to solve your problem with a flash of
> brilliance but ...

Yeah - it's got me cornered. I thought it was going to be an easy one, but
it's posing to be a real beauty at the moment!

Cheers

Adam.

Adam,

<< Unfortuantly this too will not do what we're after. This extracts the
milliseconds from the last whole minute.

If the time is 08:15, I'm of the understanding that the stored value for
the Time field in the database would be 2970000 (as the stored value is in
milliseconds from midnight).

What I'm trying to do is retrieve that actual value. (Either that, or I'm
happy to return the number of seconds or even minutes of the time since
idnight, so either 495 minutes or 29,700 seconds would also be adequate as a
result. >>

Ahh, okay.  In that case, use something like this:

SELECT CAST(<TimeStampColumn> AS TIME)-CAST('00:00:00' AS TIME)
FROM MyTable

Just skip the first CAST if the column is already a TIME column and not a
TIMESTAMP column.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> SELECT CAST(<TimeStampColumn> AS TIME)-CAST('00:00:00' AS TIME)
> FROM MyTable

Odd (to me anyway)

In DBSYS v3.30 this:-

SELECT CAST(CURRENT_TIMESTAMP AS TIME)-CAST('00:00:00' AS TIME)
FROM MyTable TOP 1

.... always gives the same number of milliseconds UNLESS you Unprepare
each time you run it.

If this is true in v4.x, then it could be a trap for young players!

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Adam H. wrote:

>
> Long time no talk! How's it going over there across the Pacific?
>

It is our quiet season here, despite the clear blue sky and the crystal
clear water of the lagoon ... I sound like a travel brochure.  It's
quiet because our major markets for visitors are the Aussies and the
Kiwis and theoretically their weather is pretty good this time of year.
And we are also supposed to be in the hurricane season (touch wood).

Quiet means more time shackled to the computer

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com