Time_Add doesn't work with mysql 5.0.18

ckacoroski

I get the ...Element Time_Add .... cannot be converted to the currently installed version of SQL. I have a few questions:

Is there a list of functions that do not work with MySQL so I can avoid them?
What are my options for manipulating timestamps with MySQL? Do I need to upgrade? Are there other forms I can use?

cheers,

ski

ckacoroski

Ok, it looks like for MySQL I can just add or subtract hours from a TimeStamp, but for Derby I have to use the Time_Add function. I still think a list of what functions are used with which database would be very useful though.

ckacoroski

Why doesn't this work

if Staff.LastModified > (Current-TimeStamp - 24) then
do something.

The minute I try any type of calculation in the query, the query becomes true and grabs all records. But I can create a separate object and do this:

SystemSettings.LastUpdated = Current_TimeStamp - 24
if Staff.LastModified > SystemSettings.LastUpdated then
do something

ski

tford

Ski,

If you are trying to perform something / check something if more than 24 hours has elapsed since the SystemSettings.LastUpdated timestamp value, I think the following will do it:

IF CURRENT_TIMESTAMP>TIME_ADD(SystemSettings.LastUpdated,24) Then ......

Does that help?
Tom

ckacoroski

Tom,

I get an error when trying to use Time_Add with mysql so I cannot use it. Your idea works find with derby though?

ski

tford

ski,

TimeAdd function works fine for me on both Derby an MySql databases. What error message are you getting?

Tom

tford

ski,

BTW - to test the TIME_ADD function, I created a BO with 2 timestamp attributes & the following rule:

TimeTest.TimeTestDisplay=TIME_ADD(TimeTest.TimeTestInput,24)

I used dynamic recalc of the rule & it properly updated TimeTest.TimeTestDisplay

Tom