September 16th, 2003, 10:36 AM
I have two field with the data type timestamp .
I want to calculate the duration of stay
how could calculate sign in - sign out
September 16th, 2003, 10:43 AM
did you try
select sign_out - sign_in from stay;
October 6th, 2003, 12:41 AM
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.
October 6th, 2003, 02:45 PM
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)