Menu
I want to count the number of months between two dates.
Doing :
SELECT TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP '2011-04-30 14:38:40'; Returns : 0 years 0 mons 409 days 20 hours 0 mins 0.00 secs
and so:
SELECT extract(month from TIMESTAMP '2012-06-13 10:38:40' - TIMESTAMP '2011-04-30 14:38:40'); returns 0.
534
Discussion: To calculate the difference between the timestamps in PostgreSQL, simply subtract the start timestamp from the end timestamp. Here, it would be arrival - departure . The difference will be of the type interval , which means you'll see it in days, hours, minutes, and seconds.
Some functions of datediff use the system current date as per query syntax. select datediff function() date1, interval date2; Explanation: In the above syntax we use select clause where datediff function means various date related functions, where date1 first specified date and date2 is second specified date.
SELECT name,end_date as left_date FROM employee WHERE end_date BETWEEN '1998-01-07' AND '2016-08-01'; In the above code, BETWEEN clause will show the name of employees who left the company on which date inclusively, means till the date that included in BETWEEN clause such as till to '2016-08-01'.
age function returns interval:
age(timestamp1, timestamp2) Then we try to extract year and month out of the interval and add them accordingly:
select extract(year from age(timestamp1, timestamp2)) * 12 + extract(month from age(timestamp1, timestamp2)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
