PostgreSQL时间处理的一些常用方式总结

1.获取当前时间

now()函数：

select now();

current_timestamp，同now()：

select current_timestamp;

select current_time;

select current_date;

可以去掉now()、掉后面的+8等：

select now()::timestamp(0)without time zone;
select current_timestamp::timestamp(0)without time zone;

2.date_part函数

语法：DATE_PART(field, source)， filed可以理解为要截取的类型。

下面是filed支持的类型：

CENTURY，世纪，获取日期所在的世纪：

select date_part(\’CENTURY\’, TIMESTAMP \’2022-12-16 12:21:13\’);
select date_part(\’CENTURY\’, now());

MILLENNIUM，千年

select date_part(\’MILLENNIUM\’, timestamp \’2022-12-16 13:21:15\’);

YEAR，年份域

select date_part(\’YEAR\’, timestamp \’2022-12-16 13:21:15\’);

MONTH，对于timestamp数值，它是一年里的月份数(1-12)；对于interval数值，它是月的数目，然后对12取模(0-11)

select date_part(\’MONTH\’, timestamp \’2022-12-16 13:21:15\’);

select date_part(\’month\’, interval \’2 years 5 months\’)

DAY，日期里的天，值是1-31：

select date_part(\’day\’, TIMESTAMP \’2022-12-16 12:21:13\’);
select date_part(\’day\’, now());

HOUR，小时(0-23)

select date_part(\’HOUR\’, TIMESTAMP \’2022-12-16 12:21:13\’);

MINUTE，分钟域(0-59)

select date_part(\’MINUTE\’, TIME \’2022-12-16 13:21:15\’);

SECOND，秒域，包括小数部分(0-59[1])

select date_part(\’SECOND\’, timestamp \’2022-12-16 13:21:15\’);

MICROSECONDS，秒域（包括小数）乘以 1,000,000

select date_part(\’MICROSECONDS\’, TIME \’2022-12-16 13:21:15\’);

MILLISECONDS，秒域（包括小数）乘以 1,000

select date_part(\’MILLISECONDS\’, timestamp \’2022-12-16 13:21:15\’);

DECADE，年份域除以10：

select date_part(\’DECADE\’, TIMESTAMP \’2022-12-16 12:21:13\’);

DOW，星期号(0-6；星期天是0) (仅用于timestamp)

select date_part(\’DOW\’, TIMESTAMP \’2022-12-16 12:21:13\’);
select date_part(\’DOW\’, now());

DOY，一年中的第几天(1 -365/366) (仅用于 timestamp)

select date_part(\’DOY\’, TIMESTAMP \’2022-12-16 12:21:13\’);

QUARTER，该天所在的该年的季度(1-4)(仅用于 timestamp)

select date_part(\’QUARTER\’, timestamp \’2022-12-16 13:21:15\’);

WEEK，该天在所在的年份里是第几周。

select date_part(\’WEEK\’, timestamp \’2022-12-16 13:21:15\’);

3.extract()函数

使用语法：extract （field from source），field 支持的类型，和date_part()函数一样

select extract (\’year\’ from timestamp \’2022-12-16 13:21:15\’)

4.日期格式化函数

to_char(timestamp, text)，把时间戳转换成字串

select to_char(now(), \’YYYY-MM-DD HH24:MI:SS\’)

to_date(text, text) 把字串转换成日期

select to_date(\’05 Dec 2022\’, \’DD Mon YYYY\’)

to_timestamp(text, text) ，把字串转换成时间戳

select to_timestamp(\’05 Dec 2022\’, \’DD Mon YYYY\’)

5.时间运算

select date \’2001-09-28\’ + integer \’7\’;
select date \’2001-09-28\’ + interval \’1 hour\’;
select date \’2001-09-28\’ + time \’03:00\’;
select interval \’1 day\’ + interval \’1 hour\’;
select timestamp \’2001-09-28 01:00\’ + interval \’23 hours\’;
select time \’01:00\’ + interval \’3 hours\’;
select – interval \’23 hours\’;
select date \’2001-10-01\’ – date \’2001-09-28\’;
select date \’2001-10-01\’ – integer \’7\’;
select date \’2001-09-28\’ – interval \’1 hour\’;
select time \’05:00\’ – time \’03:00\’;
select time \’05:00\’ – interval \’2 hours;
select timestamp \’2001-09-28 23:00\’ – interval \’23 hours\’;
select interval \’1 day\’ – interval \’1 hour\’;
select timestamp \’2001-09-29 03:00\’ – timestamp \’2001-09-27 12:00\’;
select interval \’1 hour\’ * double precision \’3.5\’;
select interval \’1 hour\’ / double precision \’1.5\’;

6.计算时间差

select now() + interval \’10 min/year/month/day/hour/sec/ (1 year 1 month 1 day 1 hour 1 min 1 sec)\’
select now() – interval \’10 min/year/month/day/hour/sec/ (1 year 1 month 1 day 1 hour 1 min 1 sec)\’
select now()::timestamp(0)without time zone-interval \’72 hour\’
select extract(day from now() – \’2001-09-27 12:00\’) from user ;