目录
获取当前日期、时间
获取当前日期
SELECT now()；
SELECT current_timestamp
两者的结果区别：now保留小数点后6位，current_timestamp留小数点后5位
获取当前时间
select localtime::time(0) as time;
select CURRENT_TIME ::time(0) as time;注：date类型是没有精度一说的（年月日），只有time和timestamp才有精度（秒后面有小数）
日期运算
日期加法
select now()::timestamp(0)+’1year’ as nextyear ;
select now()::timestamp(0)+’1month’ as nextmonth ;
select now()::timestamp(0)+’1day’ as nextday ;
select now()::timestamp(0)+’1hour’ as nexthour ;
select now()::timestamp(0)+’1minute’ as nextminute ;
select now()::timestamp(0)+’1second’ as nextsecond ;
日期减法
select now()::timestamp(0)+’-1year’ as lastyear ;
select now()::timestamp(0)+’-1month’ as lastmonth ;
select now()::timestamp(0)+’-1day’ as lastday ;
select now()::timestamp(0)+’-1hour’ as lasthour ;
select now()::timestamp(0)+’-1minute’ as lastminute ;
select now()::timestamp(0)+’-1second’ as lastsecond ;
注：日期减法时只能改变字符串中的数字为负数才生效
如果如下写法则是错误的select now()::timestamp(0)-‘1second’ as lastsecond ;混合运算
select now()::timestamp(0)+’1year 1 month 1day 1hour 1minute’ as next ;
select now()::timestamp(0)+’-1year 1 month 1day 1hour 1minute’ as last;
interval进行日期加减
使用interval进行日期加法
select to_date(‘2022-06-01′,’yyyy-mm-dd’) + interval ‘1year’ nextyear;
select to_date(‘2022-06-01′,’yyyy-mm-dd’) + interval ‘1month’ nextmonth;
select to_date(‘2022-06-01′,’yyyy-mm-dd’) + interval ‘1day’ nextday;
select to_date(‘2022-06-01′,’yyyy-mm-dd’) + interval ‘1hour’ nexthour;
select to_date(‘2022-06-01′,’yyyy-mm-dd’) + interval ‘1minute’ nextminute;
select to_date(‘2022-06-01′,’yyyy-mm-dd’) + interval ‘1second’ nextsecond;使用interval进行日期减法
select to_date(‘2022-06-01′,’yyyy-mm-dd’)+interval’-1year’ lastyear;
select to_date(‘2022-06-01′,’yyyy-mm-dd’)+ interval’-1month’ lastmonth;
select to_date(‘2022-06-01′,’yyyy-mm-dd’)+ interval’-1day’ lastday;
select to_date(‘2022-06-01′,’yyyy-mm-dd’)+ interval’-1hour’ lasthour;
select to_date(‘2022-06-01′,’yyyy-mm-dd’)+ interval’-1minute’ lastminute;
select to_date(‘2022-06-01′,’yyyy-mm-dd’)+ interval’-1second’ lastsecond;
日期精度
| Name | Storage Size | Description | Low Value | High Value | Resolution |
|---|---|---|---|---|---|
|
timestamp [ ( p ) ] [ without time zone ] |
8 bytes | both date and time (no time zone) | 4713 BC | 294276 AD | 1 microsecond / 14 digits |
|
timestamp [ ( p ) ] with time zone |
8 bytes | both date and time, with time zone | 4713 BC | 294276 AD | 1 microsecond / 14 digits |
| date | 4 bytes | date (no time of day) | 4713 BC | 5874897 AD | 1 day |
|
time [ ( p ) ] [ without time zone ] |
8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | 1 microsecond / 14 digits |
|
time [ ( p ) ] with time zone |
12 bytes | times of day only, with time zone | 00:00:00+1459 | 24:00:00-1459 | 1 microsecond / 14 digits |
|
interval [ fields ] [ ( p ) ] |
12 bytes | time interval | -178000000 years | 178000000 years | 1 microsecond / 14 digits |
日期、时间函数
| 函数 | 实例 | 结果 | 描述 |
|---|---|---|---|
| age(timestamp,timestamp) | select age(timestamp ‘2022-06-02’,timestamp ‘2000-06-01’) as birthday; | 22 years 1 day | 获取两个日期相差多少 xxx years xx mons xx days 格式 |
| age(timestamp) | select age(timestamp ‘2000-06-02:00:00:00’) as birthday | 21 years 11 mons 29 days | 距离当前日期的时间差xxx years xx mons xx days 格式 |
| date_part(text, timestamp)获取子域(等效于extract) | select date_part(‘hour’, timestamp ‘2001-02-16 20:38:40’) select date_part(‘month’, interval ‘2 years 3 months’) | 从日期中提取指定域 | |
| extract(field from timestamp) | select extract(year from timestamp ‘2022-02-16 20:38:40’) | 2022 | 从日期中提取指定域见【1】 |
注：extract函数用于从一个日期中获取某个
子集
，比如获取年，月，日，时，分，秒EPOCH对于日期和时间戳类型的值，会获取到两个日期或者时间戳参数的时间之间相隔的秒数。
SELECT EXTRACT('EPOCH' FROM (TIMESTAMP '2022-02-18 16:50:27'- TIMESTAMP '2022-02-18 16:49:23')); SELECT EXTRACT('SECOND' FROM (TIMESTAMP '2022-02-18 16:50:27'- TIMESTAMP '2022-02-18 16:49:23'));