注意事项函数性能选择高性能方式或函数客户端显示问题客户端时区影响时间类型数据的展示一、相关参考官网参考链接Spark SQL, Built-in Functions时间日期格式符号: https://spark.apache.org/docs/latest/sql-ref-datetime-pattern.html标准日期格式符号: yyyy-MM-dd HH:mm:ss、yyyy-M-ddTHH:mm:ss二、时区影响时间相关的函数大多会受时区的影响不特殊指定时区会默认使用服务器的时区spark中处理时间时最好指定session级时区并且最好指定为0时区便于进行时区偏移set spark.sql.session.timeZoneGMT8; -- 指定到东八区 set spark.sql.session.timeZoneUTC; -- 指定到零时区三、时间函数1. 受时区影响1. current_date获取当前日期select current_date() as cur_date;2018-04-092. current_timestamp/now()获取当前时间select current_timestamp() as cur_time;2018-04-09 15:20:49.2473. unix_timestamp/to_unix_timestamp从当前时区转换到unix时间这两个函数从官方文档看是相同的;从指定格式的时间转换到Unix时间unix_timestamp([timeExp[, fmt]])Returns the UNIX timestamp of current or specified time.Arguments:timeExp - A date/timestamp or string. If not provided, this defaults to current time.fmt - Date/time format pattern to follow. Ignored iftimeExpis not a string. Default value is yyyy-MM-dd HH:mm:ss. See Datetime Patterns for valid date and time format patterns.Examples: SELECT unix_timestamp(); 1476884637 SELECT unix_timestamp(2016-04-08, yyyy-MM-dd); 1460041200to_unix_timestamp(timeExp[, fmt])Returns the UNIX timestamp of the given time.Arguments:timeExp - A date/timestamp or string which is returned as a UNIX timestamp.fmt - Date/time format pattern to follow. Ignored iftimeExpis not a string. Default value is yyyy-MM-dd HH:mm:ss. See Datetime Patterns for valid date and time format patterns.Examples: SELECT to_unix_timestamp(2016-04-08, yyyy-MM-dd); 14600988004. from_unixtime从unix时间转换到当前时区的时间戳注意:第一个参数是10位的时间戳from_unixtime(unix_time[, fmt])Returnsunix_timein the specifiedfmt.Arguments:unix_time - UNIX Timestamp to be converted to the provided format.fmt - Date/time format pattern to follow. See Datetime Patterns for valid date and time format patterns. The yyyy-MM-dd HH:mm:ss pattern is used if omitted.Examples: SELECT from_unixtime(0, yyyy-MM-dd HH:mm:ss); 1970-01-01 00:00:00 SELECT from_unixtime(0); 1970-01-01 00:00:005. cast从unix时间强转到时间戳(转到设定的时区)select cast(1614815429 as timestamp);2021-03-04 07:50:296. date_format日期格式转换第一个参数必须是标准格式的时间date_format(timestamp, fmt)Convertstimestampto a value of string in the format specified by the date formatfmt.Arguments:timestamp - A date/timestamp or string to be converted to the given format.fmt - Date/time format pattern to follow. See Datetime Patterns for valid date and time format patterns.Examples: SELECT date_format(2016-04-08, y); 20161) 不受时区影响SELECT date_format(2016-04-08 18, yyyy-MM-dd);2016-04-092) 这种特定时间戳格式受时区影响select date_format(2021-06-24T22:04:5000:00, yyyy-MM-dd);2021-06-252. 不受时区影响1. 返回当前时间的unix时间戳SELECT unix_timestamp();16142375072. to_utc_timestamp时间戳从指定的时区转换到0时区to_utc_timestamp(timestamp, timezone)Given a timestamp like 2017-07-14 02:40:00.0, interprets it as a time in the given time zone, and renders that time as a timestamp in UTC. For example, GMT1 would yield 2017-07-14 01:40:00.0.Examples: SELECT to_utc_timestamp(2016-08-31, Asia/Seoul); 2016-08-30 15:00:003. from_utc_timestamp从0时区转换到指定时区from_utc_timestamp(timestamp, timezone)Given a timestamp like 2017-07-14 02:40:00.0, interprets it as a time in UTC, and renders that time as a timestamp in the given time zone. For example, GMT1 would yield 2017-07-14 03:40:00.0.Examples: SELECT from_utc_timestamp(2017-07-14 02:40:00.0, GMT1); 2017-07-14 03:40:004. to_date时间从指定格式转换到标准格式日期yyyy-MM-ddto_date(date_str[, fmt])Parses thedate_strexpression with thefmtexpression to a date. Returns null with invalid input. By default, it follows casting rules to a date if thefmtis omitted.Arguments:date_str - A string to be parsed to date.fmt - Date format pattern to follow. See Datetime Patterns for valid date and time format patterns.Examples:-- 默认按标准格式转换 SELECT to_date(2009-07-30 04:17:52); 2009-07-30 -- 指定格式转换 select to_date(20161231, yyyyMMdd); 2016-12-31 -- 指定可选格式[]代表可选项 select to_date(2024-12-02, yyyy-MM[-dd]) as dt_f; 2024-12-02 select to_date(2024-12, yyyy-MM[-dd]) as dt_f; 2024-12-015. 日期的加减(最终转成日期格式)SELECT date_add(2016-07-30 22:12:30, 1);2016-07-316. to_timestamp从指定时间格式转换到标准格式to_timestamp(timestamp_str[, fmt])Parses thetimestamp_strexpression with thefmtexpression to a timestamp. Returns null with invalid input. By default, it follows casting rules to a timestamp if thefmtis omitted. The result data type is consistent with the value of configurationspark.sql.timestampType.Arguments:timestamp_str - A string to be parsed to timestamp.fmt - Timestamp format pattern to follow. See Datetime Patterns for valid date and time format patterns.Examples: select to_timestamp(2023/5/14 2:11:00, yyyy/M/d h:mm:ss); 2023-05-14 02:11:00 select to_timestamp(20200726, yyyyMMdd); 2020-07-26 00:00:003. 客户端显示影响前端显示date、time类型的时候有可能收时区的影响为了避免不必要的麻烦可存成字符串select typeof( from_utc_timestamp(cast(cast(1697992306453 as bigint)/1000 as timestamp), GMT00:00) ) as time_ori -- timestamp类型, 客户端展示时可能转到本地时区 ,typeof( date_format(from_utc_timestamp(cast(cast(1697992306453 as bigint)/1000 as timestamp),GMT00:00), yyyy-mm-dd hh:mm:ss) ) as time_utc -- 字符串类型, 不受时区影响 ; time_ori time_utc timestamp string四、获取时间差1. 获取某个时段数据限定时间最好要闭合, 否则回溯历史数据会出问题.-- 1. sql 函数推算(日期必须是标准格式yyyy-MM-dd) select * from tbl_name where dt date_format(date_sub(2020-07-26, 2),yyyyMMdd) and dt date_format(2020-07-26,yyyyMMdd) ; -- 2. 调度直接传入 select * from tbl_name where dt 20200726 and dt 20200727 ;2. 获取时间差-- 指定时间格式获取两个时间相差的秒数 select unix_timestamp(2023/5/14 9:09:05, yyyy/M/d h:mm:ss) - unix_timestamp(2023/5/14 9:09:00, yyyy/M/d h:mm:ss) as diff_seconds; -- 指定时间格式, 获取相差天数(左参数-右参数) SELECT DATEDIFF(TO_DATE(20250705, yyyyMMdd), TO_DATE(20250703, yyyyMMdd)) AS diff_days; diff_days 2五、格式或时区转换指定时区set spark.sql.session.timeZoneUTC;不受时区影响函数to_timestamp、时区影响函数from_unixtime、from_utc_timestamp-- 1 从指定时间格式转换到标准格式 select to_timestamp(2023/5/14 2:11:00, yyyy/M/d h:mm:ss); 2023-05-14 02:11:00 select to_timestamp(20200726, yyyyMMdd); 2020-07-26 00:00:00 -- 2 从标准格式时间yyyy-MM-dd 和 yyyy-MM-dd HH:mm:ss.SSSS转到指定格式时间 select date_format(2016-04-08, yyyyMMdd); 20160408 -- 3 yyyyMMdd---yyyy-MM-dd 时间格式转换 select to_date(20161231, yyyyMMdd); 2016-12-31 select concat_ws(-,substr(20200726,1,4),substr(20200726,5,2),substr(20200726,7,2)); 2020-07-26 select date_format(to_timestamp(20200726, yyyyMMdd), yyyy-MM-dd); 2020-07-26 -- 4 从unix时间戳10位转换到当前时区时间戳, 再从当前时区转换到指定时区 -- session时区是0时区转换到东八区 set spark.sql.session.timeZoneUTC; select from_utc_timestamp(from_unixtime(0),GMT8) as dt; 1970-01-01 08:00:00日期格式符 - 格式符的数量影响显示格式具体要什么样式可以实际测试Datetime patterns - Spark 3.5.1 Documentation-- 季度的格式符号 select date_format(date 2024-05-01, q) as time; 2 select date_format(date 2024-05-01, qq) as time; 02 select date_format(date 2024-05-01, Q) as time; 2 select date_format(date 2024-05-01, QQ) as time; 02 select date_format(date 2024-05-01, QQQ) as time; Q2 select date_format(date 2024-05-01, QQQQ) as time; 2nd quarter -- 月份的格式符号 select date_format(date 1970-01-01, M); 1 select date_format(date 1970-09-01, MM); 09 select date_format(date 1970-01-01, MMM); Jan select date_format(date 1970-01-01, MMMM); January不同语言的转换२०२४-१०-१९ ०५:१६:०५ 转换到 2024-10-19 05:16:05import org.apache.hadoop.hive.ql.exec.UDF import java.text.SimpleDateFormat import java.util.TimeZone /** * 日期时间转换时区 */ class date_trans_tz extends UDF { /** * 指定要转换的时区和格式 * * param ori_date 原日期 * param ori_tz 原时区 * param tgt_tz 目标时区 * param format 日期格式 */ def evaluate(ori_date: String, ori_tz: String, tgt_tz: String, format: String): String { try { // 日期样式 val sdf new SimpleDateFormat(format) // 原时区解析日期 sdf.setTimeZone(TimeZone.getTimeZone(ori_tz)) val dateStr sdf.parse(ori_date) // 更改时区 sdf.setTimeZone(TimeZone.getTimeZone(tgt_tz)) sdf.format(dateStr.getTime) } catch { case e: Exception } } /** * 指定要转换的时区 * * param ori_date 原日期 * param ori_tz 原时区 */ def evaluate(ori_date: String, ori_tz: String, tgt_tz: String): String { val format yyyy-MM-dd HH:mm:ss try { // 日期样式 val sdf new SimpleDateFormat(format) // 原时区解析日期 sdf.setTimeZone(TimeZone.getTimeZone(ori_tz)) val dateStr sdf.parse(ori_date) // 更改时区 sdf.setTimeZone(TimeZone.getTimeZone(tgt_tz)) sdf.format(dateStr.getTime) } catch { case e: Exception } } /** * 默认转换到0时区 * * param ori_date 原日期 * param ori_tz 原时区 */ def evaluate(ori_date: String, ori_tz: String): String { val tgt_tz GMT00:00 val format yyyy-MM-dd HH:mm:ss try { // 日期样式 val sdf new SimpleDateFormat(format) // 原时区解析日期 sdf.setTimeZone(TimeZone.getTimeZone(ori_tz)) val dateStr sdf.parse(ori_date) // 更改时区 sdf.setTimeZone(TimeZone.getTimeZone(tgt_tz)) sdf.format(dateStr.getTime) } catch { case e: Exception } } }六、获取特定日期1. 静态日期处理-- 1 获取周一日期 SELECT DATE_FORMAT(DATE_TRUNC(WEEK, 2020-09-15),yyyy-MM-dd); 2020-09-14 -- 2 获取季初日期 SELECT DATE_FORMAT(DATE_TRUNC(QUARTER, 2020-09-15),yyyy-MM-dd); 2020-07-01 -- 3 获取年初日期 SELECT TRUNC(2020-09-15, YEAR); 2020-01-01 SELECT DATE_FORMAT(DATE_TRUNC(YEAR, 2020-09-15),yyyy-MM-dd); 2020-01-01 -- 4 获取月初日期 SELECT TRUNC(2020-09-15, MM); 2020-09-01 SELECT DATE_FORMAT(DATE_TRUNC(MM, 2020-09-15),yyyy-MM-dd); 2020-09-01 SELECT CONCAT(SUBSTRING(2020-09-15,1,8),01); 2020-09-01 -- 5 获取月末日期 -- last_day(date) - Returns the last day of the month which the date belongs to. SELECT LAST_DAY(2009-01-12); 2009-01-312. 动态日期处理-- 获取昨天日期 SELECT DATE_FORMAT(DATE_ADD(CURRENT_DATE(), -1),yyyMMdd); -- 获取本周一/上周一日期 SELECT DATE_FORMAT(DATE_ADD(DATE_TRUNC(WEEK, CURRENT_DATE()), 0), yyyyMMdd) AS monday_date; SELECT DATE_FORMAT(DATE_ADD(DATE_TRUNC(WEEK, CURRENT_DATE()), -7), yyyyMMdd) AS monday_date; -- 获取本月初/上月初日期 SELECT DATE_FORMAT(ADD_MONTHS(DATE_TRUNC(MM, CURRENT_DATE()), 0), yyyyMMdd) AS monday_date; SELECT DATE_FORMAT(ADD_MONTHS(DATE_TRUNC(MM, CURRENT_DATE()), -1), yyyyMMdd) AS monday_date; -- 获取本月末/上月末日期 SELECT DATE_FORMAT(ADD_MONTHS(LAST_DAY(CURRENT_DATE()), 0), yyyyMMdd) AS monday_date; SELECT DATE_FORMAT(ADD_MONTHS(LAST_DAY(CURRENT_DATE()), -1), yyyyMMdd) AS monday_date;