#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15

    Timestamp operations


    I'm having problems with timestamps

    i have timestamp fields

    WORKTIME COMPUTED BY (cast((time_end-time_start)*86400 as integer))

    this works fine, calculates worktime in seconds


    select current_timestamp, current_timestamp+1 from rdb$database - works fine, adds a day



    but i can't add a second to a timestamp value

    select current_timestamp, current_timestamp+1/86400 from rdb$database - doesn't add anything

    select current_timestamp, current_timestamp+1.0/86400.0 from rdb$database - doesn't add anything

    select current_timestamp, cast(current_timestamp+1.0/86400.0 as timestamp) from rdb$database - doesn't add anything
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    25
    Rep Power
    0
    Hi!

    Why don't you use the "built in" UDFs?

    Code:
    DECLARE EXTERNAL FUNCTION ADDDAY
        DATE,
        INTEGER
    RETURNS DATE
    ENTRY_POINT 'addDay' MODULE_NAME 'fbudf';
    
    DECLARE EXTERNAL FUNCTION ADDHOUR
        DATE,
        INTEGER
    RETURNS DATE
    ENTRY_POINT 'addHour' MODULE_NAME 'fbudf';
    
    DECLARE EXTERNAL FUNCTION ADDMILLISECOND
        DATE,
        INTEGER
    RETURNS DATE
    ENTRY_POINT 'addMilliSecond' MODULE_NAME 'fbudf';
    
    DECLARE EXTERNAL FUNCTION ADDMINUTE
        DATE,
        INTEGER
    RETURNS DATE
    ENTRY_POINT 'addMinute' MODULE_NAME 'fbudf';
    
    DECLARE EXTERNAL FUNCTION ADDMONTH
        DATE,
        INTEGER
    RETURNS DATE
    ENTRY_POINT 'addMonth' MODULE_NAME 'fbudf';
    
    DECLARE EXTERNAL FUNCTION ADDSECOND
        DATE,
        INTEGER
    RETURNS DATE
    ENTRY_POINT 'addSecond' MODULE_NAME 'fbudf';
    
    DECLARE EXTERNAL FUNCTION ADDWEEK
        DATE,
        INTEGER
    RETURNS DATE
    ENTRY_POINT 'addWeek' MODULE_NAME 'fbudf';
    
    DECLARE EXTERNAL FUNCTION ADDYEAR
        DATE,
        INTEGER
    RETURNS DATE
    ENTRY_POINT 'addYear' MODULE_NAME 'fbudf';
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    you must read this link and you'll edify :
    http://www.firebirdsql.org/manual/qsg10-firebird-sql.html
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    Originally Posted by nagysz
    I'm having problems with timestamps

    i have timestamp fields

    WORKTIME COMPUTED BY (cast((time_end-time_start)*86400 as integer))

    this works fine, calculates worktime in seconds


    select current_timestamp, current_timestamp+1 from rdb$database - works fine, adds a day



    but i can't add a second to a timestamp value

    select current_timestamp, current_timestamp+1/86400 from rdb$database - doesn't add anything

    select current_timestamp, current_timestamp+1.0/86400.0 from rdb$database - doesn't add anything

    select current_timestamp, cast(current_timestamp+1.0/86400.0 as timestamp) from rdb$database - doesn't add anything
    In Firebird 2.1 there is one very beautiful function called DATEADD Firebirds' release notes It's all you need.

    For :
    Code:
    WORKTIME COMPUTED BY (cast((time_end-time_start)*86400 as integer))
    look for DATEDIFF function.
    Code:
    WORKTIME COMPUTED BY ( DATEDIFF(DAY from time_start to time_end))
    Last edited by mIRCata; September 17th, 2010 at 09:37 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15
    Originally Posted by mIRCata
    In Firebird 2.1 there is one very beautiful function called DATEADD Firebirds' release notes It's all you need.

    For :
    Code:
    WORKTIME COMPUTED BY (cast((time_end-time_start)*86400 as integer))
    look for DATEDIFF function.
    Code:
    WORKTIME COMPUTED BY ( DATEDIFF(DAY from time_start to time_end))

    Thanks a lot for all of you have answered, this last solution is the closest to my heart

IMN logo majestic logo threadwatch logo seochat tools logo