Here I need to calculate the difference of the two dates in the PostgreSQL.
In SQL Server: Like we do in SQL Server its much easier.
DATEDIFF(Day, MIN(joindate), MAX(joindate)) AS DateDifference;
My Try: I am trying using the following script:
(Max(joindate) - Min(joindate)) as DateDifference;
Question:
Is my method correct?
Is there any function in
PostgreSQLto calculate this?
6 Answers
Your calculation is correct for DATE types, but if your values are timestamps, you should probably use EXTRACT (or DATE_PART) to be sure to get only the difference in full days;
EXTRACT(DAY FROM MAX(joindate)-MIN(joindate)) AS DateDifference
An SQLfiddle to test with. Note the timestamp difference being 1 second less than 2 full days.
CAST both fields to datatype DATE and you can use a minus:
(CAST(MAX(joindate) AS date) - CAST(MIN(joindate) AS date)) as DateDifference
Test case:
SELECT (CAST(MAX(joindate) AS date) - CAST(MIN(joindate) AS date)) as DateDifference
FROM
generate_series('2014-01-01'::timestamp, '2014-02-01'::timestamp, interval '1 hour') g(joindate);
Result: 31
Or create a function datediff():
CREATE OR REPLACE FUNCTION datediff(timestamp, timestamp)
RETURNS int
LANGUAGE sql
AS
$$
SELECT CAST($1 AS date) - CAST($2 AS date) as DateDifference
$$;
a simple way would be to cast the dates into timestamps and take their difference and then extract the DAY part.
if you want real difference
select extract(day from 'DATE_A'::timestamp - 'DATE_B'::timestamp);
if you want absolute difference
select abs(extract(day from 'DATE_A'::timestamp - 'DATE_B'::timestamp));
This is how I usually do it. A simple number of days perspective of B minus A.
DATE_PART('day', MAX(joindate) - MIN(joindate)) as date_diff
This is not a direct answer to OP's question, however, this is the answer I was looking for when my search brought me to this thread.
For someone who wants to create a human-readable string that denotes the difference between two dates, consider using AGE().
AGE(table.end_date, table.start_date)
It produces results that looks like these:
0:00:27
3 days 22:06:39.833264
4 days 01:12:39.473559
20 days 17:53:20.23287
23 days 21:01:15.150703
1 mon 19 days 01:52:24.262275
2 mons 17 days 05:04:12.277099
2 mons 17 days 04:59:21.618069
If you have a timestamp already you will need not to cast anything,
For the absolute difference @zubair-0 answer is great here is implementation an using...
select abs(extract(day from A - B)) from table_name;
Note: A and B are timestamps as data types
