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.