Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Unanswered: Conditional operators on time / date data types

    All,

    Note that the code below (in particular the if conditional) only
    works if __extend_auction_threshold is an hour or greater.
    Otherwise the if statement always resolves to false. For example attempting
    to use __extend_auction_threshold='00:03:00' does not ever seem
    to resolve to 'true' for the if statement.

    Is there anything you can see that I'm missing here?

    Thanks in advance.

    -------------------------------

    data type key:

    end_time: timestamp with timezone
    extend_auction_threshold: time without time zone
    extend_auction_by_time: time without time zone

    note that local vars (__xxxx) used as place holders are defined
    as the same data types in the plpgsql as their corresponding columns of the auctions table.

    ------------------------------

    select end_time into __end_time from auctions where id = _auction_id;
    select extend_auction_threshold into __extend_auction_threshold from auctions where id = _auction_id;

    select extend_auction_by_time into __extend_auction_by_time from auctions where id = _auction_id;

    -- if we are in the last 3 minutes of the auction
    if (now() + __extend_auction_threshold > __end_time) then

    -- then extend the auction by the specified time...
    __new_end_time := __end_time + __extend_auction_by_time;
    update auctions set end_time = __new_end_time where id = _auction_id;

    end if;

  2. #2
    Join Date
    Sep 2010
    Posts
    4

    Question Problem Revision

    The IF statement below only resolves to 'true' if __extend_auction_threshold (time without time zone) is an hour or greater.
    Otherwise the IF statement always resolves to false.

    For example with __extend_auction_threshold:='00:03:00', the IF statement
    does not ever seem to resolve to 'true'. But with __extend_auction_threshold:='03:00:00' then the
    IF statement will resolve to 'true' if we are within
    3 hours of the auction ending.

    NOTE: The table columns referenced match the data types used for the local vars below.

    Code:
    DECLARE
    ...
    end_time timestamp with time zone
    extend_auction_threshold time without time zone
    extend_auction_by_time time without time zone
    ...
    BEGIN
    ...
    select end_time into __end_time from auctions where id = _auction_id;
    select extend_auction_threshold into __extend_auction_threshold from auctions where id = _auction_id;
    
    select extend_auction_by_time into __extend_auction_by_time from auctions where id = _auction_id;
    
    -- if we are in the last 3 minutes of the auction
    if (now() + __extend_auction_threshold > __end_time) then
    
      -- then extend the auction by the specified time...
      __new_end_time := __end_time + __extend_auction_by_time;
      update auctions set end_time = __new_end_time where id = _auction_id;
    
    end if
    ...

  3. #3
    Join Date
    May 2008
    Posts
    277
    A quick review of the documentation reveals that adding two times together is not a valid operation. extend_auction_threshold and extend_auction_time should both be intervals, not times.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •