Hi,

Another date / time / timestamp question...

I'm wanting to store a time interval in a table, and have the ability to
use SQL to work with that interval.

For instance, I want a template that lists a set of tasks, and how long
they take to complete.

My first thought was to store this as a TimeStamp value. (This way I can
store intervals for tasks that are greater than 1 day).

The problem I'm facing is when it comes to SQL. I am going to want to be
able to use this value to extract details such as the estimated end time
given the number of jobs as well as the starting time stamp.

ie: for a order where we will be for 3 jobs of the same tasks where
StartTime is a Timestamp field as well:


Select StartTime, StartTime + (Jobcount * TaskEstInterval) as EstEndDate


The problem that I'm facing is that working with TimeStamp fields in
DBISAM SQL appears to be quite difficult. From the manual I'm aware that
TimeStamp fields are a 64bit floating point numbers representing
cumalative millseconds but I can't seem to access this information from
SQL to work with my calculations.

(I assumed that I could simply add one timestamp to another to get a 3rd
value, but in DBSYS this doesn't appear to be the case).

Am I going down the wrong path? Can anyone recommend what would be the
best way to record this information, in a way where I can easily
manipulate it with SQL and end up with TimeStamps as a result?

Thanks & regards

Adam.

Adam


Quick bit of experimenting in DBSys shows that timestamp + interval is adding milliseconds to the time portion so store the interval as milliseconds then you can add it to the timestamp and it should be right

Roy Lambert

Hi Roy,

Thanks for your help...

> Quick bit of experimenting in DBSys shows that timestamp + interval is adding milliseconds to the time portion so store the interval as milliseconds then you can add it to the timestamp and it should be right

Thanks for that. Is there a way to convert a Timestamp to milliseconds
in SQL?

It appears as though if I subtract one timestamp from another I get a
value in millseconds, but I can't find a way of getting a value to start
with.

My thoughts are since a Timestamp is already just millseconds - I would
like to use a Timestamp in my application - but I can't find a way to
convert a timestamp to milliseconds in the firstplace with SQL.

Otherwise, is there a 'correct' method / ISO Standard of storing
intervals in general programming language? The beauty about TimeStamps
is that there are already data aware components available for it, but if
this isn't possible I want to make sure that I do the most popular for
whatever may rear it's head further down the track.  

Cheers

Adam.

Adam

This works


select _received ,
_received  - cast('1900-01-01' AS TIMESTAMP)
from mandn
where _received is not null

The delphi date/time system starts (I think) from 1/1/1900 but you could pick almost any convenient date and use that, as long as you're consistent it doesn't really matter.

The above will give you a tinestamp (_received) and a number of milliseconds since 1/1/1900

Roy Lambert

Hi Roy,

Thanks for that - I can look at using that.

I'm guessing there's no particular standard when it comes to dealing
with intervals - that milliseconds is as good as anything.

Thanks for your help!

Adam.

Adam


>I'm guessing there's no particular standard when it comes to dealing
>with intervals - that milliseconds is as good as anything.

There is in the newer SQL standards - ElevateDB implements INTERVAL

Roy