Tuesday, December 2, 2008

Setting CURRENT TIMESTAMP

The point of this post is NOT what the function does, in fact it's SO DANGEROUS it deserves a warning label:

DO NOT USE THIS FUNCTION ON A PRODUCTION SERVER!

No, the point is to demonstrate some of the cooler but lesser-known features of SQL Anywhere, like date arithmetic and sending commands to the operating system.

Here's the code for a SQL Anywhere user-defined function that sets the computer's clock, in effect changing the value of CURRENT TIMESTAMP:
CREATE FUNCTION set_current_timestamp (
IN @target_timestamp TIMESTAMP )
RETURNS TIMESTAMP
NOT DETERMINISTIC
BEGIN
DECLARE @echo_string VARCHAR ( 100 );

IF @target_timestamp IS NULL THEN
RETURN CURRENT TIMESTAMP;
END IF;

SET @echo_string = STRING (
'echo ',
DATEFORMAT ( @target_timestamp, 'DD-MM-YY' ), -- CAUTION !!!
'>echo_string.txt' );

CALL xp_cmdshell ( @echo_string, 'no_output' );
CALL xp_cmdshell ( 'DATE <echo_string.txt', 'no_output' );

SET @echo_string = STRING (
'echo ',
DATEFORMAT ( @target_timestamp, 'HH:NN:SS' ),
'>echo_string.txt' );

CALL xp_cmdshell ( @echo_string, 'no_output' );
CALL xp_cmdshell ( 'TIME <echo_string.txt', 'no_output' );

RETURN CURRENT TIMESTAMP;

END; -- set_current_timestamp
Here's some code which sets the system clock backwards and forwards by one day, and by one hour:
BEGIN
DECLARE @ts1 TIMESTAMP;
DECLARE @ts2 TIMESTAMP;
DECLARE @ts3 TIMESTAMP;
DECLARE @ts4 TIMESTAMP;

CALL set_current_timestamp ( DATEADD ( DAY, -1, CURRENT TIMESTAMP ) );
SET @ts1 = CURRENT TIMESTAMP;

CALL set_current_timestamp ( DATEADD ( DAY, +1, CURRENT TIMESTAMP ) );
SET @ts2 = CURRENT TIMESTAMP;

CALL set_current_timestamp ( DATEADD ( HOUR, -1, CURRENT TIMESTAMP ) );
SET @ts3 = CURRENT TIMESTAMP;

CALL set_current_timestamp ( DATEADD ( HOUR, +1, CURRENT TIMESTAMP ) );
SET @ts4 = CURRENT TIMESTAMP;

SELECT @ts1, @ts2, @ts3, @ts4;
END;

@ts1,@ts2,@ts3,@ts4
'2008-11-28 13:17:43.031',
'2008-11-29 13:17:43.015',
'2008-11-29 12:17:43.031',
'2008-11-29 13:17:43.015'
The calls to set_current_timestamp use the DATEADD function to do the date arithmetic. Inside the function, the xp_cmdshell function is used to execute the ECHO, DATE and TIME commands (this has only been tested on Windows XP):
ECHO '28-11-08'>echo_string.txt
DATE <echo_string.txt
ECHO '13:17:43'>echo_string.txt
TIME <echo_string.txt
Sure, it's brute force... but it sure helped with the testing I did when writing yesterday's Exploring DEFAULT TIMESTAMP

Speaking of date arithmetic, here's a snippet of code from last week's posting MobiLink Fall Back that's worth explaining:
-- If the current timestamp and the last download timestamp 
-- are both within the "fall back hour", set the last download
-- timestamp back to 1:00 AM to catch all changes made during
-- the two hour period between 1 and 2 AM.

IF DOW ( @current_timestamp ) = 1 -- Sunday
AND DAY ( @current_timestamp ) <= 7 -- first Sunday
AND MONTH ( @current_timestamp ) = 11 -- first Sunday in November
AND HOUR ( @current_timestamp ) = 1 -- between 1 and 2 AM
THEN
SET @fall_back_hour_starting_timestamp
= DATETIME ( STRING (
YEAR ( @current_timestamp ),
'-11-',
DAY ( @current_timestamp ),
' 01:00:00.000' ) );

IF @last_download_timestamp
>= @fall_back_hour_starting_timestamp THEN
SET @last_download_timestamp
= @fall_back_hour_starting_timestamp;
MESSAGE STRING (
'SET @last_download_timestamp = ',
@last_download_timestamp ) TO CONSOLE;
END IF;

END IF;
Here's what the functions do:
  • DOW returns the day of the week, numbered 1 for Sunday through 7 for Saturday.

  • DAY returns the day number 1 through 31.

  • MONTH returns the month number 1 through 12.

  • HOUR returns the hour number 0 through 23 (ha, ha, got you... it's a steenking offset, not a hour number... the first day of the month might be numbered 1 but the first hour of the day is zero!).

  • DATETIME converts a formatted VARCHAR value to a TIMESTAMP.

  • STRING is the all-powerful function that automatially converts all the arguments to VARCHAR, coalescing all NULL arguments to '' along the way, and concatenates all the results into a single string.

  • YEAR returns the four digit year number.
There are a ton of date and time functions in SQL Anywhere, more than in many client-side application development languages:



...it's worth going through the list just to see what's there.

No comments: