0. Postgres中的常用时间类型
--日期
date
--时间
time
--日期+时间
timestamp
--时间长度
interval
1. 计算时间差
PostgreSQL 没有DATEDIFF()函数,可以用DATE_PART()实现该功能:
MySQL | PostgreSQL | |
---|---|---|
Years | DATEDIFF(yy, start, end) | DATE_PART(‘year’, end) - DATE_PART(‘year’, start) |
Months | DATEDIFF(mm, start, end) | years_diff * 12 + (DATE_PART(‘month’, end) - DATE_PART(‘month’, start)) |
Days | DATEDIFF(dd, start, end) | DATE_PART(‘day’, end - start) |
Weeks | DATEDIFF(wk, start, end) | TRUNC(DATE_PART(‘day’, end - start)/7) |
Hours | DATEDIFF(hh, start, end) | days_diff * 24 + DATE_PART(‘hour’, end - start ) |
Minutes | DATEDIFF(mi, start, end) | hours_diff * 60 + DATE_PART(‘minute’, end - start ) |
Seconds | DATEDIFF(ss, start, end) | minutes_diff * 60 + DATE_PART(‘minute’, end - start ) |
差多少小时
--In MySQL
SELECT DATEDIFF(hour, '2011-12-30 08:55', '2011-12-30 09:05');
--注意:实际时间不到1小时时,DATEDIFF会返回1,而DATE_PART返回0
--In PostgreSQL
SELECT DATE_PART('day', '2011-12-30 08:55'::timestamp - '2011-12-30 09:05'::