[RESOLVED] SQL query nearest time/timestamp datetime issue

[whtrader]

Hi,

I need help solving this. I am running this query in MAccess.

Table : TankGaugingDB(TimeRecorded as Timestamp,DataX as Integer)
Table : InduDB(TimeStart as DateTime,TimeStop as DateTime)

SELECT     
InduDB.TimeStart, InduDB.TimeStop, TankGaugingDB_1.DataX, TankGaugingDB.DataX AS Expr3,
TankGaugingDB.DataX + TankGaugingDB_1.DataX AS Tank_Gauging, 
FROM         
((InduDB INNER JOIN
TankGaugingDB ON InduDB.TimeStart = TankGaugingDB.TimeRecorded) INNER JOIN
TankGaugingDB AS TankGaugingDB_1 ON InduDB.TimeStop = TankGaugingDB_1.TimeRecorded)

The issues:

1) The TimeRecorded is store as MSQL timestamp which e.g "2005-09-18 10:00:00" where as the TimeStart and TimeStop is store in MAccess with datetime datatype. Is it possible to query using the query above?

2) Also as both the table are populated by different application the (TimeStart,TimeStop) and (TimeRecorded) will not be exact by the second. So my question is can this query work?
Please advice.

[anguswalker]

There should be no problem with comparing, but the query cannot work reliably if TimeRecorded and TimeStart are not guaranteed to be the same. Is there no way you could update InduDB.TimeStart and InduDB.TimeStop directly from TankGaugingDB.TimeRecorded, so that you do know they are the same?

[whtrader]

Hi anguswalker,

Thank you for you speedy reply. I am not sure what do you mean by

Is there no way you could update InduDB.TimeStart and InduDB.TimeStop directly from TankGaugingDB.TimeRecorded, so that you do know they are the same?

Could you please elaborate?

[anguswalker]

What I mean is, I assume from your description that InduDB is on a PC and enters the system time into .TimeStart and .TimeStop when the tank filling begins and ends, whereas TankGauging is on a different machine serving MySQL and timestamps with its system time (which is why the times may not be identical). What I wondered was, when a record is created in InduDB, could you not put into .TimeStart and .TimeStop the actual values from TankGauging.Timerecorded rather than the system time.
I may of course be completely wrong about how your app works. Probably a fuller description would help.

[whtrader]

Hi anguswalker,

I see where you are going here. I'll test this out. I have one more question though,

Also as both the table are populated by different application the (TimeStart,TimeStop) and (TimeRecorded) will not be exact by the second.

There should be no problem with comparing

Does this mean the MAccess will compensate for the difference as stated above? Thank you.

[anguswalker]

What I'm suggesting though is that you copy the value from TankGauging.Timerecorded into InduDB.Timestart. So it will be exact to the second.

[whtrader]

Hi anguswalker,

Actually there are two system monitoring the fillings. Both application are generating records hence the difference in time . My goal here is to extract and display data from Application 1(DataX) using the TimeStart and TimeStop from Application 2. I can't think of a way to copy the TimeRecorded to the TimeStart since there are no define relationship between the two database.

Application 1: TankGauging in (PC A)
Table : TankGaugingDB(TimeRecorded as Timestamp,DataX as Integer)
Database :MSQL
Generates record PerMinute(interval)

Application 2: Indu in (PC B)
Table : InduDB(TimeStart as DateTime,TimeStop as DateTime)
Database :MAccess
Generates record from external trigger

I hope this clears things up.

Thank you.

[whtrader]

Hi,

Is it possible to edit a string in a query?
Like this InduDB.TimeStart = "2005-09-19 01:14:23" Add 1 to the 14 to this "2005-09-19 01:15:23" for querying sake.

[Ecniv]

cdate(<field>) + cdate("00:01:00")

or just

<field> + cdate("00:01:00")

[whtrader]

Solved