There's a half-price sale going on in the SAP store... until December 31 almost all of the SQL Anywhere SKUs have been marked down by 50%:
Monday, September 30, 2013
SQL Anywhere Half-Price Sale
Friday, September 27, 2013
Product Suggestion: More ISQL SET Option Commands
ISQL already HAS most of what I was going to suggest: ISQL commands to change option settings on the fly, as part of a batch of commands being processed by ISQL.
Did you know that?
Here's an example:
SET OPTION TRUNCATION_LENGTH = 70;
SELECT PROPERTY ( 'RememberLastStatement' );
SET OPTION TRUNCATION_LENGTH = 50;
SELECT PROPERTY ( 'RememberLastStatement' );
SET OPTION TRUNCATION_LENGTH = 30;
SELECT PROPERTY ( 'RememberLastStatement' );
PROPERTY('RememberLastStatement')
----------------------------------------------------------------------
Yes
(1 rows)
PROPERTY('RememberLastStatement')
--------------------------------------------------
Yes
(1 rows)
PROPERTY('RememberLastStatemen
------------------------------
Yes
(1 rows)
|
There's one option missing, however; there is no SET OPTION command to dynamically choose Tools - Options - SQL Anywhere - Results - Style - Text, you have to use the GUI:
Maybe a SET OPTION statement isn't appropriate here, maybe a dbisql command line option is better... it doesn't matter... what matters is the ability to change the option in a batch file without having to clickety-clack through the GUI whenever one or the other styles is more appropriate.
Wednesday, September 25, 2013
SAP TechEd 2013 Registration Fee Goes Up at 5:01 PM PDT This Friday
The registration fee for SAP TechEd 2013 in Las Vegas on October 21–25 goes up after this Friday: from US $2,695 to $2,795.
Monday, September 23, 2013
Latest SQL Anywhere Updates: 12.0.1.3958 for Mac OS
The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.
- Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1, 16.0 and On Demand) are shown here.
- The "EBF 21788 SP60" numbers are the new SAP-specific codes associated with the build numbers "12.0.1.3894".
- Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.
Friday, September 20, 2013
Force Balance a + b + c = 100
The requirement is to display three integer percentage values a, b and c, where a + b + c always equals 100.
The values a and b come from table t, and c is calculated as the remainder 100 - a - b.
The problem is, the values of a and b may be too large, causing a + b to exceed 100. In this case the values of a and/or b must be lowered so that a + b = 100 (thus making c = 0).
Here are nine combinations of a and b, where 4 combinations are OK and five combinations exhibit the problem:
The trick is, how should the new "lowered" a and/or b values be calculated?
CREATE TABLE t ( pkey INTEGER PRIMARY KEY, a INTEGER, b INTEGER ); INSERT t VALUES ( 1, 0, 0 ); INSERT t VALUES ( 2, 10, 10 ); INSERT t VALUES ( 3, 100, 0 ); INSERT t VALUES ( 4, 0, 100 ); INSERT t VALUES ( 5, 55, 55 ); INSERT t VALUES ( 6, 200, 0 ); INSERT t VALUES ( 7, 0, 200 ); INSERT t VALUES ( 8, 90, 70 ); INSERT t VALUES ( 9, 150, 50 ); COMMIT; SELECT pkey, a, b, 100 - a - b AS c FROM t ORDER BY pkey; pkey a b c ----------- ----------- ----------- ----------- 1 0 0 100 2 10 10 80 3 100 0 0 4 0 100 0 5 55 55 -10 6 200 0 -100 7 0 200 -100 8 90 70 -60 9 150 50 -100
What's your solution? (don't peek!)
This is a real-world problem taken from the development of a new feature in Foxhound Version 3: The "Busy Wait Idle %" column will show the relative amount of time each connection has spent doing work, waiting for resources, and sitting idle with nothing to do.
Busy + Wait + Idle must add up to 100 because, well, this is the real world, but the SQL Anywhere performance properties used to calculate Busy and Wait aren't always [cough] in step... sometimes Busy + Wait exceeds 100, hence the need to "pull them down" so Busy + Wait = 100.
One way is to recalculate a and b as percentages of ( a + b ):
This process is sometimes called "force balance" where a column of rounded numbers are fiddled, er, adjusted so they add up to a known total (like 100%) rather than displaying some lame explanation about "rounding errors"... everyone knows that rounded numbers are inherently imprecise, so as long as the adjustment is made in a sensible manner it's sometimes better than displaying numbers that don't add up.
SELECT pkey, IF t.a + t.b <= 100 THEN t.a ELSE CAST ( ( t.a * 100 ) / ( t.a + t.b ) AS SMALLINT ) END IF AS a, IF t.a + t.b <= 100 THEN t.b ELSE 100 - a END IF AS b, 100 - a - b AS c FROM t ORDER BY pkey; pkey a b c ----------- ----------- ----------- ----------- 1 0 0 100 2 10 10 80 3 100 0 0 4 0 100 0 5 50 50 0 6 100 0 0 7 0 100 0 8 56 44 0 9 75 25 0
The force balance process often requires row-by-row processing; in the example shown here, it's nice to be able to embed the calculations in a query.
Monday, September 16, 2013
Latest SQL Anywhere Updates: V16 for Linux, Mac and Windows, 11 for Linux
The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.
- Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1, 16.0 and On Demand) are shown here.
- The "EBF 21788 SP60" numbers are the new SAP-specific codes associated with the build numbers "12.0.1.3894".
- Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.
Monday, September 9, 2013
Implementing YEARDIFF
Question: How do I compute the number of complete years between two timestamps?
Answer: Call DATEDIFF YEAR and if you don't like the answer, subtract 1!
That's not as silly as it sounds. DATEDIFF has two huge advantages over a start-from-scratch-and-do-it-yourself approach:
- DATEDIFF takes care of those pesky leap years and the funky divisible-by-4-vs-100-vs-400 rule (2000 and 2400 are leap years, while 1800, 1900, 2100, 2200, 2300 and 2500 are NOT leap years), and
- DATEDIFF works... it's already been tested by bazillions of developers and end users.
But wait!
There's another function you can call to determine if DATEDIFF got it right or not: DATEADD.Yes, DATEADD... take the number of years returned by DATEDIFF and call DATEADD to add it to the first timestamp (assuming the first timestamp is the smaller one). If the result is less than or equal to the second timestamp, then DATEDIFF returned the right number. If not, then DATEADD's answer is too high by 1... hence the "subtract 1" in the answer above.
Here's the code, followed by a test using timestamps that are different by only one microsecond, as well as one year boundary:
CREATE FUNCTION YEARDIFF (
IN @timestamp1 TIMESTAMP,
IN @timestamp2 TIMESTAMP )
RETURNS SMALLINT
DETERMINISTIC
BEGIN
DECLARE @lesser_timestamp TIMESTAMP;
DECLARE @greater_timestamp TIMESTAMP;
DECLARE @sign SMALLINT;
DECLARE @yeardiff SMALLINT;
IF @timestamp1 IS NULL OR @timestamp1 IS NULL THEN
RETURN NULL;
END IF;
IF @timestamp1 = @timestamp2 THEN
RETURN 0;
END IF;
IF @timestamp1 < @timestamp2 THEN
SET @sign = 1;
SET @lesser_timestamp = @timestamp1;
SET @greater_timestamp = @timestamp2;
ELSE
SET @sign = -1;
SET @lesser_timestamp = @timestamp2;
SET @greater_timestamp = @timestamp1;
END IF;
SET @yeardiff = DATEDIFF ( YEAR, @lesser_timestamp, @greater_timestamp );
IF DATEADD ( YEAR, @yeardiff, @lesser_timestamp ) > @greater_timestamp THEN
RETURN @sign * ( @yeardiff - 1 );
ELSE
RETURN @sign * @yeardiff;
END IF;
END;
SELECT DATEDIFF ( YEAR, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_1,
YEARDIFF ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_1,
DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_2,
YEARDIFF ( '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS YEARDIFF_2;
DATEDIFF_YEAR_1 YEARDIFF_1 DATEDIFF_YEAR_2 YEARDIFF_2
--------------- ---------- --------------- ----------
1 0 -1 0
SELECT DATEDIFF ( YEAR, '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_3,
YEARDIFF ( '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_3,
DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_4,
YEARDIFF ( '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS YEARDIFF_4;
DATEDIFF_YEAR_3 YEARDIFF_3 DATEDIFF_YEAR_4 YEARDIFF_4
--------------- ---------- --------------- ----------
2 1 -2 -1
|
Here's another test that how a straightforward call to DATEDIFF flubs a simple age calculation but gets it right when YEARDIFF handles the call to DATEDIFF:
SELECT DATEDIFF ( YEAR, '2012-07-27', '2013-07-28' ) AS correct_age,
DATEDIFF ( YEAR, '2012-07-29', '2013-07-28' ) AS incorrect_age;
correct_age incorrect_age
----------- -------------
1 1
SELECT YEARDIFF ( '2012-07-27', '2013-07-28' ) AS correct_age_1,
YEARDIFF ( '2012-07-29', '2013-07-28' ) AS correct_age_2;
correct_age_1 correct_age_2
------------- -------------
1 0
|
Wednesday, September 4, 2013
Beware DATEDIFF Alternatives
Previously on . . . The story began with Documenting DATEDIFF and continued with three episodes about the use and abuse of DATEDIFF in this blog, in Foxhound and in the Help.
Now the story turns to seven other SQL Anywhere functions that can be used instead of DATEDIFF to compute the difference between two timestamps.
Here's a comparison of how these seven functions stack up against DATEDIFF when applied to the same two timestamp values that are exactly one microsecond apart:
SELECT 'DATEDIFF' AS "function",
DATEDIFF ( year, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year,
DATEDIFF ( month, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month,
DATEDIFF ( day, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day,
DATEDIFF ( week, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week,
DATEDIFF ( hour, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour,
DATEDIFF ( minute, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute,
DATEDIFF ( second, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second
UNION ALL
SELECT 'YEARS, etc',
YEARS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year,
MONTHS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month,
DAYS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day,
WEEKS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week,
HOURS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour,
MINUTES ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute,
SECONDS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second;
function year month day week hour minute second
-------- ---- ----- --- ---- ---- ------ ------
DATEDIFF 1 1 1 1 0 1 0
YEARS, etc 1 1 1 0 0 1 1
|
Here's what the Help has to say about these seven alternatives to DATEDIFF, and how the Help stacks up against testing:
| What The Help Says | What Testing Says | |
| YEARS | The value of YEARS is computed by counting the number of first days of the year between the two dates. | True. YEARS returns the number of year boundaries between the two parameters, just like DATEDIFF YEAR. |
| MONTHS | If you pass two TIMESTAMP values to the MONTHS function, the function returns the integer number of months between them. | False. MONTHS returns the number of month boundaries between the two parameters, just like DATEDIFF MONTH. |
| DAYS | If you pass two TIMESTAMP values to the DAYS function, the function returns the integer number of days between them. | False. DAYS returns the number of day boundaries between the two parameters, just like DATEDIFF DAY. |
| WEEKS | Given two dates (Syntax 2), the WEEKS function returns the number of weeks between them. The WEEKS function is similar to the DATEDIFF function, however the method used to calculate the number of weeks between two dates is not the same and can return a different result. The return value for WEEKS is determined by dividing the number of days between the two dates by seven, and then rounding down. However, DATEDIFF uses number of week boundaries in its computation. This can cause the values returned from the two functions to be different. For example, if the first date is a Friday and the second date is the following Monday, the WEEKS function returns a difference of 0, but the DATEDIFF function returns a difference of 1. While neither method is better than the other, you should consider the difference when choosing between WEEKS and DATEDIFF. | True. This is the best Help of all these functions, DATEDIFF included... it gives a clear explanation of the difference between counting units (weeks, etc.) and counting boundaries (week changes, etc). |
| HOURS | If you pass two TIMESTAMP values to the HOURS function, the function returns the integer number of hours between them. | True. Both HOURS and DATEDIFF HOUR return the number of hours, not the number of hour boundaries. |
| MINUTES | If you pass two TIMESTAMP values to the MINUTES function, the function returns the integer number of minutes between them. | False. MINUTES returns the number of minute boundaries between the two parameters, just like DATEDIFF MINUTE. |
| SECONDS | If you pass two TIMESTAMP values to the SECONDS function, the function returns the integer number of seconds between them as a SIGNED BIGINT value. | False. SECONDS returns the number of second boundaries between the two parameters. This is different from DATEDIFF SECOND which returns the number of seconds rather than the number of boundaries. |
So . . .
WEEKS makes a nice (and nicely documented) alternative to DATEDIFF WEEK, but the rest of them?Monday, September 2, 2013
DATEDIFF Mistakes Case Study 3: The Help
Previously on . . . The story began with Documenting DATEDIFF and continued with two episodes about the use and abuse of DATEDIFF in this blog and in Foxhound.
Now the story turns to DATEDIFF examples published in the SQL Anywhere 16 Help.
The Scoring System
- FAIL means the DATEDIFF usage is badly flawed; it shouldn't have been coded that way, and it should be fixed.
- LUCKY means the DATEDIFF usage may be flawed but it doesn't matter given the data values involved.
- OK means the DATEDIFF usage is OK given the data types involved.
The Scores
Example 1 is OK: begin_poll event
Let's ignore the obvious problems with this code (the "Syntax error near DISTINCT", the unknown nature of the tables in the FROM clause, and so on) and assume that "datediff( hour, last_status_change, now()" refers to a valid TIMESTAMP value in last_status_change.
This example creates a push request for a Notifier named Notifier A. It uses a SQL statement that inserts rows into a table named PushRequest. Each row in this table represents a message to send to an address. The WHERE clause determines which push requests are inserted into the PushRequest table.
To use the ml_add_property system procedure with a SQL Anywhere consolidated database, run the following command:
ml_add_property( 'SIS', 'Notifier(Notifier A)', 'begin_poll', 'INSERT INTO PushRequest (gateway, mluser, subject, content) SELECT ''MyGateway'', DISTINCT mluser, ''sync'', stream_param FROM MLUserExtra, mluser_union, Dealer WHERE MLUserExtra.mluser = mluser_union.name AND (push_sync_status = ''waiting for request'' OR datediff( hour, last_status_change, now() ) > 12 ) AND ( mluser_union.publication_name is NULL OR mluser_union.publication_name =''FullSync'' ) AND Dealer.last_modified > mluser_union.last_sync_time' );
Guess what? Assuming that "> 12" means "thirteen full hours or more" then DATEDIFF HOUR works OK because unlike other forms of DATEDIFF it returns the number of HOUR units between the two timestamps, not the number of HOUR boundaries.
Here's proof using two timestamps that have 13 hour boundaries between them but differ by an interval of only 12:00:00.000001 (which DATEDIFF HOUR truncates to 12):
BEGIN DECLARE @last_status_change TIMESTAMP DEFAULT ( '2013 09 02 00:59:59.999999' ); DECLARE @now TIMESTAMP DEFAULT ( '2013 09 02 13:00:00.000000' ); SELECT DATEDIFF ( HOUR, @last_status_change, @now ); END; DATEDIFF(hour,@last_status_change,@now) 12
Example 2 is a FAIL: Trigger conditions for events
Assuming Notification.last_notification is a TIMESTAMP column, what does "DATEDIFF( minute, last_notification, current timestamp )" return?
Notify an administrator of a possible attempt to break into the database:
CREATE EVENT SecurityCheck TYPE ConnectFailed HANDLER BEGIN DECLARE num_failures INT; DECLARE mins INT; INSERT INTO FailedConnections( log_time ) VALUES ( CURRENT TIMESTAMP ); SELECT COUNT( * ) INTO num_failures FROM FailedConnections WHERE log_time >= DATEADD( minute, -5, current timestamp ); IF( num_failures >= 3 ) THEN SELECT DATEDIFF( minute, last_notification, current timestamp ) INTO mins FROM Notification; IF( mins > 30 ) THEN UPDATE Notification SET last_notification = current timestamp; CALL xp_sendmail( recipient='DBAdmin', subject='Security Check', "message"= 'over 3 failed connections in last 5 minutes' ) END IF END IF END;
Answer: It returns the number of MINUTE boundaries between the two timestamp values. This usage must be marked as a FAIL because DATEDIFF MINUTE can return two different answers for exactly the same interval depending on the number of MINUTE boundaries between the two timestamps.
BEGIN DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 00:59:59.999999' ); DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.000000' ); SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp ); END; BEGIN DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 01:00:00.000000' ); DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.000001' ); SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp ); END; DATEDIFF(minute,@last_notification,@current_timestamp) 31 DATEDIFF(minute,@last_notification,@current_timestamp) 30DATEDIFF MINUTE can also return a smaller answer for a longer interval. Here's proof; the first DATEDIFF MINUTE call returns 31 for an interval of 00:30:00.000001 while the second returns 30 for an interval of 00:30:00.999999:
BEGIN DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 00:59:59.999999' ); DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.000000' ); SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp ); END; BEGIN DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 01:00:00.000000' ); DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.999999' ); SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp ); END; DATEDIFF(minute,@last_notification,@current_timestamp) 31 DATEDIFF(minute,@last_notification,@current_timestamp) 30
Example 3 is OK: #hook_dict table
This usage scores OK for two reasons:
The following sample sp_hook_dbmlsync_delay procedure illustrates the use of in/out parameters in the #hook_dict table. The procedure allows synchronization only outside a scheduled down time of the MobiLink system between 18:00 and 19:00.
CREATE PROCEDURE sp_hook_dbmlsync_delay() BEGIN DECLARE delay_val integer; SET delay_val=DATEDIFF( second, CURRENT TIME, '19:00'); IF (delay_val>0 AND delay_val<3600) THEN UPDATE #hook_dict SET value=delay_val WHERE name='delay duration'; END IF; END
- The code's more precise than the specs so an error doesn't matter; i.e., the specifications refer to hours "between 18:00 and 19:00" while the code calculates seconds "DATEDIFF( second, CURRENT TIME, '19:00')".
- ...and besides, DATEDIFF SECOND returns the number of full seconds between two timestamps rather than the number of second boundaries; i.e., it's the other DATEDIFF rarity along with with DATEDIFF HOUR.
Example 4 is OK: sp_hook_dbmlsync_abort
This one looks like a FAIL until you realize that down_time_start contains a constant value '19:00:00.000000'. That means it doesn't matter whether DATEDIFF HOUR is calculated using hours (it's not) or hour boundaries (it is), it's all the same.
The following procedure prevents synchronization during a scheduled maintenance hour between 19:00 and 20:00 each day.
CREATE PROCEDURE sp_hook_dbmlsync_abort() BEGIN DECLARE down_time_start TIME; DECLARE is_down_time VARCHAR(128); SET down_time_start='19:00'; IF datediff( hour,down_time_start,now(*) ) < 1 THEN set is_down_time='true'; ELSE SET is_down_time='false'; END IF; UPDATE #hook_dict SET value = is_down_time WHERE name = 'abort synchronization' END;
In other words, this usage is OK.
Example 5 is OK: sa_performance_diagnostics system procedure
This code's OK for the same reasons Example 3 is OK: For the connections of interest the difference between LoginTime and CURRENT TIMESTAMP grows very large very quickly compared with the size of any error introduced by DATEDIFF, and besides, DATEDIFF SECOND is calculated in seconds rather than second boundaries.
You can execute the following query to identify connections that have spent a long time waiting for database server requests to complete.
SELECT Number, Name, CAST( DATEDIFF( second, LoginTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS T, IF T <> 0 THEN (ReqTimeActive / T) ELSE NULL ENDIF AS PercentActive FROM sa_performance_diagnostics() WHERE T > 0 AND PercentActive > 10.0 ORDER BY PercentActive DESC;
Example 6 is OK: sa_performance_diagnostics system procedure
Same thing again, same as Examples 3 and 5: This code is OK. A purist might use DATEDIFF MILLISECOND (Foxhound does) but with a predicate like "ReqTime > 60.0" it's not really necessary.
Find all requests that are currently executing, and have been executing for more than 60 seconds:
SELECT Number, Name, CAST( DATEDIFF( second, LastReqTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS ReqTime FROM sa_performance_diagnostics() WHERE ReqStatus <> 'IDLE' AND ReqTime > 60.0 ORDER BY ReqTime DESC;
Example 7 is a FAIL: Text index refresh types
Let's ignore all the issues with this code and assume the DATEDIFF call was really written like this:
You can define your own strategy for refreshing MANUAL REFRESH text indexes. In the following example, all MANUAL REFRESH text indexes are refreshed using a refresh interval that is passed as an argument, and rules that are similar to those used for AUTO REFRESH text indexes.
CREATE PROCEDURE refresh_manual_text_indexes( refresh_interval UNSIGNED INT ) BEGIN FOR lp1 AS c1 CURSOR FOR SELECT ts.* FROM SYS.SYSTEXTIDX ti JOIN sa_text_index_stats( ) ts ON ( ts.index_id = ti.index_id ) WHERE ti.refresh_type = 1 -- manual refresh indexes only DO BEGIN IF last_refresh_utc IS null OR cast(pending_length as float) / ( IF doc_length=0 THEN NULL ELSE doc_length ENDIF) > 0.2 OR DATEDIFF( MINUTE, CURRENT UTC TIMESTAMP, last_refresh_utc ) > refresh_interval THEN EXECUTE IMMEDIATE 'REFRESH TEXT INDEX ' || text-index-name || ' ON "' || table-owner || '"."' || table-name || '"'; END IF; END; END FOR; END;
DATEDIFF( MINUTE, last_refresh, CURRENT TIMESTAMP ) > refresh_intervalThis code is a FAIL for the same reasons as the code in Example 2:
- DATEDIFF MINUTE can return two different answers for exactly the same interval depending on the number of MINUTE boundaries between the two timestamps, and
- DATEDIFF MINUTE can also return a smaller answer for a longer interval.
The Final Score: 5 OK, 2 FAIL
Some might argue one or two OK scores were really just LUCKY, but it's the two FAILs that prove the point: DATEDIFF is difficult.
Subscribe to:
Posts (Atom)
