Hi,

I have a table with a timestamp field called "LastUpdate" and I am trying to select records that was updated in the last 3 hours.  I tried to use "EXTRACT" clause but it's not really the what want since it select records based on seconds, minutes, ..etc.  Below is my draft SQL statmnet.

SELECT * FROM TB_SALES WHERE (CURRENT_TIMESNAP - LastUpdate) = 3

I know "3" does not mean anything in this statement but I'm looking to make it the hours deference.

Any help will be much appreciated.

Regards,
Abdulaziz Jasser

Abdulaziz Jasser wrote:

> SELECT * FROM TB_SALES WHERE (CURRENT_TIMESNAP - LastUpdate) = 3

This is how I do this sort of thing. It can be parameterised. Not sure
if it helps you though.


var
   szTimeInfo : String;
   tmOldTime : TDateTime;
begin
   tmOldTime := IncMinute(Now, -30);
   szTimeInfo := BxQuotedDateTimeToSQLStr(tmOldTime, true);

xWorkQuery.SQL.Text := 'DELETE FROM CustomerAccount
         WHERE (caCreateDate < ' + szTimeInfo + ')';

--

Matthew Jones

Would this work for you? It's using INTERVAL and current_timestamp() to fetch rows where LastUpdate falls in the last 3 hours.

SELECT * FROM TB_SALES
WHERE LastUpdate BETWEEN (CURRENT_TIMESTAMP() - INTERVAL '3' HOUR) AND CURRENT_TIMESTAMP()

Matthew Jones wrote:

> BxQuotedDateTimeToSQLStr(tmOldTime, true);

For the record:

function BxQuotedDateTimeToSQLStr(tmDateTime : TDateTime; bMilitaryTime
: boolean = true) : String;
begin
{$IFDEF USE_ELEVATE_DB}
   Result := ' TIMESTAMP ' +
Engine.QuotedSQLStr(Engine.DateTimeToSQLStr(tmDateTime, bMilitaryTime));
{$ELSE}
   Result := Engine.QuotedSQLStr(Engine.BxDateTimeToAnsiStr(tmDateTime,
bMilitaryTime));
{$ENDIF}
end;

It's my EDB/DBISAM compatible function, nothing clever.

--

Matthew Jones

Matthew and Phil,

Thanks to you booth.  However, Phil has the right answer.
Regards,
Abdulaziz Jasser

Abdulaziz Jasser wrote:

> Phil has the right answer

Indeed, for some purposes the pure SQL is better. What I like about
passing in the exact time as text is that I can vary it according to
program options, so I can set a 3 hour window for normal use, but 15
seconds for testing, or similar. You can probably do that in SQL too,
but I'm old school. 8-)

--

Matthew Jones

"Matthew Jones" wrote:

Matthew  wrote:

<<Indeed, for some purposes the pure SQL is better. What I like about
passing in the exact time as text is that I can vary it according to
program options, so I can set a 3 hour window for normal use, but 15
seconds for testing, or similar. You can probably do that in SQL too,
but I'm old school. 8-)>>


I do see your point and totally agree.  However, I am using this SQL statement inside a job for synchronization purposes which is really a case-customer specific.  That is why I use SQL to configure things out of my application.  However, I like to use organized code in terms of using universal functions/procedures inside my code to avoid redundancy and maintain strong control over the changes.  

By the way. I came from a hybrid school (old and modrern)  
Regards,
Abdulaziz Jasser

Matthew


I'm on your side. I'm an antique. I am starting to use SQL a bit more but I do get surprised by the amount of effort some people put into achieving an SQL solution when it could be done quickly and easily in Delphi (or C++ or whatever).

I can sort of understand it when bog standard runs everywhere SQL is used but not when it uses supplier specific enhancements or features that not everyone has implemented.


Roy Lambert

>>I am starting to use SQL a bit more but I do get surprised
>>by the amount of effort some people put into achieving an
>>SQL solution when it could be done quickly and easily in Delphi

I agree Roy. I often fall down on this, doing lots of work to get something to work with cumbersome SQL Scripts rather than beautiful object oriented Delphi code. I think its partly as Abdulaziz says, because of working with JOBs or PROCEDUREs, without a Delphi EXE to hand.

However EDB's ability to integrate external modules is really powerful ... allowing optimized Delphi EXE code to be called straight from EDB. I have used it a bit, but not enough.

New Year's Resolution: More external modules.