1. 概述 观远BI主要使用日期（Date）和日期时间（Timestamp）两种类型来展示日期和时间数据。通过日期函数，可以对日期和时间数据进行截取、比较计算、格式转换等多种操作。
类型 | 标准格式 | 示例 | 日期 （Date） | yyyy-MM-dd | 2024-01-01 | 日期时间 （Timestamp） | yyyy-MM-dd HH:mm:ss | 2024-01-01 20:00:00 |
2. 函数列表 2.1. 时间或日期截取函数2.1.1. 提取特定的日期部分，返回数值用途 | 函数 | 示例 [Time]: 2024-02-10 08:09:10 | 结果 | 年份 | year() | year( [Time] ) | 2024 | 季度（1~4） | quarter() | quarter( [Time ) | 1 | 月份（1~12） | month() | month( [Time] ) | 2 | 按月取天（1~31） | dayofmonth() | dayofmonth( [Time] ) | 10 | 按年取天（1~365） | dayofyear() | dayofyear( [Time] ) | 41 | 星期 （按周取天） | dayofweek()星期日为1; weekday() 星期一为 0 | dayofweek( [Time] )-1; weekday( [Time] )+1 | 6 | 小时 （0~23） | hour() | hour( [Time] ) | 8 | 分钟（0~59） | minute() | minute( [Time] ) | 9 | 秒（0~59） | second() | second( [Time] ) | 10 | 周数（1~53） | weekofyear() | weekofyear( [Time] ) | 6 | 提取任一指定部分 （field参数参考下表） | extract(field FROM source) | extract(h FROM [Time]) 取小时 | 8 | date_part(field, source) datepart(field, source) | date_part('H', [Time]) 取小时 | 8 |
常用格式参数： 含义 | extract/date_part 适用参数 （返回不含前导0的数字） | trunc/date_trunc 适用参数 (返回Date/Timestamp) | 年 | "YEAR", "Y", "YEARS", "YR", "YRS"； "YEAROFWEEK"（ISO年周） | "YEAR","YYYY","YY" | 季度 | "QUARTER", "QTR" | "QUARTER" | 月 | "MONTH", "MON", "MONS", "MONTHS" | "MONTH","MM","MON" | 周 | "WEEK", "W", "WEEKS" | "WEEK" | 天 | "DAY", "D", "DAYS" | "DAY","DD" | 小时（24H制） | "HOUR", "H", "HOURS", "HR", "HRS" | "HOUR" | 分钟 | "MINUTE", "M", "MIN", "MINS", "MINUTES" | "MINUTE" | 秒 | "SECOND", "S", "SEC", "SECONDS", "SECS" | "SECOND" | 按周取天 | "DAYOFWEEK"，"DOW"； "DAYOFWEEK_ISO", "DOW_ISO" | / | 按年取天（1~365） | "DOY" | / |
注意: - 不区分大小写；
- 引号必须是半角符号，单引号双引号都可以用；extract() 函数不能加引号
- 天('day'/'dd')及以下单位（时分秒）不能用于 trunc()函数。
2.1.2. 根据指定的时间单位将日期截断到指定粒度，返回日期或时间date_trunc(format, [字段]) --按照目标格式截取日期或日期时间后返回timestamp
trunc([字段], format) --按照目标格式截取日期或日期时间后返回date
--format参数参考上表
用途 | 函数 | 示例 [Time]: 2024-02-10 08:09:10 | 结果 | 取所在周的周一 | trunc(date,'week') | trunc([Time], 'week') | 2024-02-05 | date_trunc('week',ts) | date_trunc('week', [Time]) | 2024-02-05 00:00:00 | 取所在月第一天 | trunc(date,'MM') | trunc([Time], 'MM') | 2024-02-01 | date_trunc('MM',ts) | date_trunc('MM', [Time]) | 2024-02-01 00:00:00 | 取所在月最后一天 | last_day(date) | last_day([Time]) | 2024-02-29 | 取所在季第一天 | trunc(date,'quarter') | trunc([Time], 'quarter') | 2024-01-01 | date_trunc('quarter', ts) | date_trunc('quarter', [Time]) | 2024-01-01 00:00:00 | 取所在年第一天 | trunc(date,'year') | trunc([Time],'year') | 2024-01-01 | date_trunc('yyyy', ts) | date_trunc('yyyy', [Time]) | 2024-01-01 00:00:00 | 截取时间日期到天 （之后归零） | date_trunc('day', ts) | date_trunc('day', [Time]) | 2024-02-10 00:00:00 | 截取时间日期到小时（之后归零） | date_trunc('hour', ts) | date_trunc('hour', [Time]) | 2024-02-10 08:00:00 | 截取时间日期到分钟（之后归零） | date_trunc('minute', ts) | date_trunc('minute', [Time]) | 2024-02-10 08:09:00 | 取下一个星期几的日期 | next_day(start_date, day_of_week) | next_day( [Time],'TU') next_day([Time],'sunday') | 2024-02-13 2024-02-11 |
2.2. 日期时间生成函数用途 | 函数 | 示例 | 结果 | 当前时间 | now() | now() | 2024-02-10 08:09:10 | current_timestamp() | localtimestamp() | 今天的日期 | current_date() | current_date() | 2024-02-10 | curdate() | 当前时间戳 | unix_timestamp() | unix_timestamp() | 1707523750 | 当前时区 | current_timezone() | current_timezone() | Asia/Shanghai | 用年月日的数字组合成日期 | make_date(year, month, day) | make_date(2024,2,10) | 2024-02-10 | 用年月日时分秒的数字组合成日期时间 | make_timestamp(year, month, day, hour, min, sec[, timezone]) | make_timestamp(2024,2,10,8,9,10) | 2024-02-10 08:09:10 | make_timestamp_ltz(year, month, day, hour, min, sec[, timezone]) | make_timestamp_ltz(2024,2,10,8,9,10,'CET') | 2024-02-10 15:09:10 | make_timestamp_ntz(year, month, day, hour, min, sec) | make_timestamp_ntz(2024,2,10,8,9,10) | 2024-02-10 08:09:10 |
2.3. 日期时间计算用途 | 函数 | 示例 [Time]: 2024-02-10 08:09:10 | 结果 | 增减日期时间 | [字段] +/- INTERVAL 1 YEAR/MONTH/WEEK/DAY/HOUR/MINUTE 数值为整数，不能引用其它字段 | [Time] - interval 1 year | 2023-02-10 08:09:10 | [Time] + interval 2 hour 30 minute | 2024-02-10 10:39:10 | 计算未来日期 (不保留时间) | date_add(date, num) dateadd(date, num) | date_add([Time], 3) | 2024-02-13 | add_months(date, num) 数值为正整数 | add_months([Time], 1) | 2024-03-10 | 计算过去日期 (不保留时间) | date_sub(date, num) | date_sub([Time], 3) | 2024-02-07 | add_months(start_date, num) 数值为负整数 | add_months([Time], -1) | 2024-01-10 | 计算日期差 | datediff(endDate, startDate) date_diff(endDate, startDate) 结果为整数 | datediff([Time],'2024-02-09 20:00:00') | 1 | 计算月份差 | months_between(endTime, startTime) 结果为浮点数 | months_between([Time],'2024-01-01 00:00:00') | 1.301281 | 计算分钟差 | *自定义函数: MINUTEDIFF() | MINUTEDIFF([Time],'2024-02-10 20:00:00') | -710 | 计算秒差 | *自定义函数: SECONDDIFF() | SECONDDIFF([Time],'2024-02-10 20:00:00') | -42650 |
2.4. 日期与时间日期转换用途 | 函数 | 示例 [Time]: 2024-02-10 08:09:10 [Ts_str]: 2024/2/10 08:09:10 | 结果 | 转换为日期型 (Date) | to_date(date_str[, fmt]) 标准格式时fmt可省略，此时可与 date(date_str) 和 cast(date_str as date) 通用 | to_date([Ts_str],'yyyy/M/d') | 2024-02-10 | date([Time]) | 2024-02-10 | cast([Time] as date) | 转换为时间日期型(Timestamp) | to_timestamp(ts_str[, fmt]) 标准格式时fmt可省略，此时可与 timestamp(ts_str) 和 cast(ts_str as timestamp) 通用 | to_timestamp([Ts_str],'yyyy/M/d HH:mm:ss') | 2024-02-10 08:09:10 | timestamp(date([Time])) | 2024-02-10 00:00:00 | cast(date([Time]) as timestamp) | 将日期时间转换为字符串 | string(expr) | string('2024-02-10 08:09:10') | 2024-02-10 08:09:10 | CAST(expr AS string) | cast([Time] as string) | 将日期时间转换为指定格式的字符串 | date_format(ts, fmt) | date_format([Time], 'HH:mm:ss') | 08:09:10 | 将日期时间转换为时间戳数字（自1970-01-01以来的秒数） | to_unix_timestamp(timeExp[, fmt]) | to_unix_timestamp([Ts_str],'yyyy/M/dd HH:mm:ss') | 1707523750 | unix_timestamp(timeExp[, fmt]) 标准格式时fmt可省略 | unix_timestamp([Time]) | 1707523750 | 时间戳转为日期时间 | from_unixtime(unix_time[, fmt]) 自动按当前时区转换 | from_unixtime(1) | 1970-01-01 08:00:01 | 日期转换为unix date数字（自1970-01-01以来的天数） | unix_date(date) | unix_date(date([Time])) | 19763 | unix date换为日期 | date_from_unix_date(days) | date_from_unix_date(19763) | 2024-02-10 | 时区偏移换算 | from_utc_timestamp(ts, timezone) 用世界标准时间推算目标时区时间 | from_utc_timestamp([Time], 'Asia/Shanghai') ； from_utc_timestamp([Time], 'GMT+8') | 2024-02-10 16:09:10 | |
to_utc_timestamp(ts, timezone) 用指定时区时间去推算世界标准时间 | to_utc_timestamp([Time], 'Asia/Shanghai') ; to_utc_timestamp([Time], 'GMT+8') | 2024-02-10 00:09:10 | |
convert_timezone([sourceTz, ]targetTz, sourceTs) 把时间转换为目标时区的时间 | convert_timezone('Asia/Shanghai','America/Los_Angeles',[Time]) | 2024-02-09 16:09:10 |
常用日期时间格式化的参数 DateFormatter（注意区分大小写）：
符号 | 含义 | 举例 | yy/yyyy | yy: 不包含纪元的2位数年份; yyyy: 包括纪元的4位数的年份。 | 24; 2024 | M/MM | 月份数字。M: 没有前导零; MM: 一位数的月份有一个前导零。 | 2; 02 | MMM/MMMM | MMM:月份的英文缩写名称; MMMM:月份的英文完整名称 | Jan; January | d/dd | 按月取天。d: 没有前导零; dd: 一位数的日期有一个前导零。 | 1~31; 01~31 | D/DD | 按年取天。D: 没有前导零; DD: 一位数的日期有一个前导零。 | 1~365; 01~365 | h/hh | 12小时制的小时。h: 没有前导零; hh: 一位数的小时数有前导零。 | 1~12; 01~12 | H/HH | 24小时制的小时。H: 没有前导零; HH: 一位数的小时数有前导零 | 0~23; 00~23 | m/mm | 分钟。m: 没有前导零; mm: 一位数的分钟数有一个前导零。 | 0~59; 00~59 | s/ss | 秒。s: 没有前导零; ss: 一位数的秒数有前导零。 | 0~60; 00~60 | S | 秒的分数小数，用于秒的更精细统计和显示。 | 978 | E/EEEE | 1至 3 个 E：周中某天星期的缩写名称； 4 个 E：周中某天星期的全称 | Tue; Tuesday | a | 上午下午 | AM；PM | z/zzzz | 当前时区名字。 z: 时区缩写；zzzz：时区名字全称 | CST；China Standard Time | Z | 当前时区偏移 | +0800 | X | 当前时区偏移。X: +08; XX: +0800; XXX: +08:00 | +08:00 | F | 当前月内第几个自然周。 从1号开始每7天为一个自然周。 | date_format('2021-08-22','EEEE') 返回 Sunday；date_format('2021-08-22','F') 返回 4。 综合判断 2021-08-22 是 2021 年 8 月的第 4 个周日。 |
更多的Spark函数和用法请参考 官方文档；观远内的使用案例请参考 Spark日期和时间函数使用案例。 |
