Hi Team,
   I have the following sql in edbmgr..
{sql}
select * from jobtickets
where (current_timestamp - lastmodified) > INTERVAL '1' HOUR
order by buscode, jobno
{sql}

   When I click 'Prepare' I get the following error..
>> ElevateDB Error #700 An error was found in the statement at line 2 and column 44 (Expected Interval Day To MSecond expression but instead found INTERVAL '1' HOUR)<<

 lastmodified is a timestamp field.

   Am I misunderstanding what is required??

Regards & TIA,
Ian

Ian


If you don't use them frequently INTERVALs are the spawn of the anti-god of your choice (hoping that doesn't offend anyone)


Your problem comes from fact that you're trying to compare a time with a timestamp so you need to convert. Try

(CURRENT_TIMESTAMP - LastModified) HOUR > INTERVAL '1' HOUR,


Roy Lambert

ps if it makes you feel any better its just taken me 15 inutes to figure out.

Ah Ha!  Thanks Roy.

That little Caveat is missing in the help.

Regards,
Ian

Ian,

<< That little Caveat is missing in the help. >>

It's covered here:

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

under "Date, Time, and Timestamp Subtraction".

Tim Young
Elevate Software
www.elevatesoft.com

Tim


Just wondering, would it be possible to have it as a CAST eg

CAST ((CURRENT_TIMESTAMP - LastModified) AS HOUR) > INTERVAL '1' HOUR,

doeen't gain or loose anything but might be easier for people to understand.

Roy Lambert

Roy,

<< Just wondering, would it be possible to have it as a CAST eg >>

You can, but you would have to use the full interval type name:

CAST ((CURRENT_TIMESTAMP - LastModified) AS INTERVAL HOUR) > INTERVAL '1' HOUR,

Tim Young
Elevate Software
www.elevatesoft.com

Tim


If its a trivial addition I think it would be worthwhile for those who use INTERVAL at extended intervals

Roy Lambert

Roy,

<< If its a trivial addition I think it would be worthwhile for those who use INTERVAL at extended intervals >>

I'm not sure I understand what you're saying - it's *already* possible to do what you indicated.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

My misunderstanding. I thought you were saying that this is what the syntax would have to be not that it was already there


Roy Lambert

Roy,

<< My misunderstanding. I thought you were saying that this is what the syntax would have to be not that it was already there >>

No problem.

Tim Young
Elevate Software
www.elevatesoft.com