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:

1. Is there a list of functions that do not work with MySQL so I can avoid them?

2. 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