阿西河

所有教程

公众号
🌙
阿西河前端的公众号

我的收藏

    最近访问  (文章)

      教程列表

      抓包专区
      测试专区

      PostgreSQL 时间 / 日期的函数和操作符

      PostgreSQL 时间 / 日期函数和操作符

      日期 / 时间操做符

      下表演示了基本算术操作符的行为 (+,*, 等):

      操作符例子结果
      +date ‘2001-09-28’ + integer ‘7’date ‘2001-10-05’
      +date ‘2001-09-28’ + interval ‘1 hour’timestamp ‘2001-09-28 01:00:00’
      +date ‘2001-09-28’ + time ‘03:00’timestamp ‘2001-09-28 03:00:00’
      +interval ‘1 day’ + interval ‘1 hour’interval ‘1 day 01:00:00’
      +timestamp ‘2001-09-28 01:00’ + interval ‘23 hours’timestamp ‘2001-09-29 00:00:00’
      +time ‘01:00’ + interval ‘3 hours’time ‘04:00:00’
      -- interval ‘23 hours’interval ‘-23:00:00’
      -date ‘2001-10-01’ - date ‘2001-09-28’integer ‘3’ (days)
      -date ‘2001-10-01’ - integer ‘7’date ‘2001-09-24’
      -date ‘2001-09-28’ - interval ‘1 hour’timestamp ‘2001-09-27 23:00:00’
      -time ‘05:00’ - time ‘03:00’interval ‘02:00:00’
      -time ‘05:00’ - interval ‘2 hours’time ‘03:00:00’
      -timestamp ‘2001-09-28 23:00’ - interval ‘23 hours’timestamp ‘2001-09-28 00:00:00’
      -interval ‘1 day’ - interval ‘1 hour’interval ‘1 day -01:00:00’
      -timestamp ‘2001-09-29 03:00’ - timestamp ‘2001-09-27 12:00’interval ‘1 day 15:00:00’
      *900 * interval ‘1 second’interval ‘00:15:00’
      *21 * interval ‘1 day’interval ‘21 days’
      *double precision ‘3.5’ * interval ‘1 hour’interval ‘03:30:00’
      /interval ‘1 hour’ / double precision ‘1.5’interval ‘00:40:00’

      日期 / 时间函数

      函数返回类型描述例子结果
      age(timestamp, timestamp)interval减去参数后的"符号化"结果,使用年和月,不只是使用天age(timestamp ‘2001-04-10’, timestamp ‘1957-06-13’)43 years 9 mons 27 days
      age(timestamp)intervalcurrent_date减去参数后的结果(在午夜)age(timestamp ‘1957-06-13’)43 years 8 mons 3 days
      clock_timestamp()timestamp with time zone实时时钟的当前时间戳(在语句执行时变化)  
      current_datedate当前的日期;  
      current_timetime with time zone当日时间;  
      current_timestamptimestamp with time zone当前事务开始时的时间戳;  
      date_part(text, timestamp)double precision获取子域(等效于extract);date_part(‘hour’, timestamp ‘2001-02-16 20:38:40’)20
      date_part(text, interval)double precision获取子域(等效于extract);date_part(‘month’, interval ‘2 years 3 months’)3
      date_trunc(text, timestamp)timestamp截断成指定的精度;date_trunc(‘hour’, timestamp ‘2001-02-16 20:38:40’)2001-02-16 20:00:00
      date_trunc(text, interval)interval截取指定的精度,date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’)2 days 03:00:00
      extract(field from timestamp)double precision获取子域;extract(hour from timestamp ‘2001-02-16 20:38:40’)20
      extract(field from interval)double precision获取子域;extract(month from interval ‘2 years 3 months’)3
      isfinite(date)boolean测试是否为有穷日期(不是 +/- 无穷)isfinite(date ‘2001-02-16’)true
      isfinite(timestamp)boolean测试是否为有穷时间戳(不是 +/- 无穷)isfinite(timestamp ‘2001-02-16 21:28:30’)true
      isfinite(interval)boolean测试是否为有穷时间间隔isfinite(interval ‘4 hours’)true
      justify_days(interval)interval按照每月 30 天调整时间间隔justify_days(interval ‘35 days’)1 mon 5 days
      justify_hours(interval)interval按照每天 24 小时调整时间间隔justify_hours(interval ‘27 hours’)1 day 03:00:00
      justify_interval(interval)interval使用justify_daysjustify_hours调整时间间隔的同时进行正负号调整justify_interval(interval ‘1 mon -1 hour’)29 days 23:00:00
      localtimetime当日时间;  
      localtimestamptimestamp当前事务开始时的时间戳;  
      make_date(year int, month int, day int)date为年、月和日字段创建日期make_date(2013, 7, 15)2013-07-15
      make_interval(years int DEFAULT 0, months int DEFAULT 0, weeks int DEFAULT 0, days int DEFAULT 0, hours int DEFAULT 0, mins int DEFAULT 0, secs double precision DEFAULT 0.0)interval从年、月、周、天、小时、分钟和秒字段中创建间隔make_interval(days := 10)10 days
      make_time(hour int, min int, sec double precision)time从小时、分钟和秒字段中创建时间make_time(8, 15, 23.5)08:15:23.5
      make_timestamp(year int, month int, day int, hour int, min int, sec double precision)timestamp从年、月、日、小时、分钟和秒字段中创建时间戳make_timestamp(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5
      make_timestamptz(year int, month int, day int, hour int, min int, sec double precision, [ timezone text ])timestamp with time zone从年、月、日、小时、分钟和秒字段中创建带有时区的时间戳。 没有指定 timezone 时,使用当前的时区。make_timestamptz(2013, 7, 15, 8, 15, 23.5)2013-07-15 08:15:23.5+01
      now()timestamp with time zone当前事务开始时的时间戳;  
      statement_timestamp()timestamp with time zone实时时钟的当前时间戳;  
      timeofday()textclock_timestamp相同,但结果是一个 text 字符串;  
      transaction_timestamp()timestamp with time zone当前事务开始时的时间戳;  
      目录
      目录