Thread: timestamp

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

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    12

    timestamp


    I have two field with the data type timestamp .

    I want to calculate the duration of stay

    how could calculate sign in - sign out
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    France
    Posts
    55
    Rep Power
    13

    Re: timestamp


    did you try
    Code:
    select sign_out - sign_in from stay;
    ?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    12

    last week last month


    Just wondering for the timestamp data type
    How could I select those sign_in that is last week, last month, last year.
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    PostgreSQL has some very sophisticated types, functions and operators for date/time needs. You should take a look at the INTERVAL datatype specifically, as well as the Date/Time Functions and Operators and the Data Type Formatting Functions

    Essentially, whenever you subtract or add timestamps, you should cast the result to interval type, as in:
    SELECT (sign_out - sign_in)::interval from stay;

    Then, you can add that expression to the WHERE clause:

    SELECT (sign_out - sign_in)::interval AS the_time_span FROM stay
    WHERE the_time_span < '30 days'::interval;

    This, for example, will select every row where the "stay" was less than 1 month.

    (The INTERVAL type can take human-readable, natural-language input, so that for example '1 month 2 days'::interval is the same as '32 days'::interval, or '4 days 2 hours'::interval is the same as '50 hours'::interval)
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo