SPARK的时间函数

最新推荐文章于 2024-10-15 11:38:13 发布

原创最新推荐文章于 2024-10-15 11:38:13 发布 · 575 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#spark时间函数

spark 专栏收录该内容

7 篇文章

订阅专栏

本文展示了如何在Spark中使用各种时间函数，包括current_date、current_timestamp、dayofweek、dayofyear、weekofyear、trunc、date_trunc、date_format、unix_timestamp、from_unixtime、to_timestamp、months_between、add_months、last_day、date_sub和datediff等，通过实例代码进行操作演示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SPARK的时间函数

package test

import java.util.Date

import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

/**

Created by fanxiaoxia on 2018/11/28.
*/
object SPARKDATE {

Class.forName(“oracle.jdbc.driver.OracleDriver”)
val url = “jdbc:oracle:thin:test/test@//192.168.1.123:1521/orcl”

def main(args: Array[String]): Unit = {
//加载数据库中表
val conf = new SparkConf().setAppName(“A”).setMaster(“local[*]”)

val spark = SparkSession.builder().config(conf).getOrCreate()

val jdbcDF = spark.read.format("jdbc").options(
  Map("url" -> url,
    "user" -> "test1",
    "password" -> "test1",
    "dbtable" -> "XL_CARD_INFO")).load()



jdbcDF.createOrReplaceTempView("XL_CARD_INFO")  //从oracle读到的数据注册成临时表

// val date = new Date() //常规创建日期对象；

//获取当前时间

// val sql = spark.sql(
// “”"
// |select current_date from XL_CARD_INFO
// “”".stripMargin)
// .show()

// current_timestamp/now()获取当前时间

// val sql = spark.sql(
// “”"
// |select current_timestamp from XL_CARD_INFO
// “”".stripMargin)
// .show()

//时间戳转换字符串格式

// val sql = spark.sql(
// “”"
// |select
// |REACH_TIME
// | from XULINV_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//从日期时间中提取字段

//year,month,day/dayofmonth,hour,minute,second

// val sql = spark.sql(
// “”"
// |select
// |month(TXNDATETIME)
// | from XULINV_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//打印出来不正确
//dayofweek (1 = Sunday, 2 = Monday, …, 7 = Saturday),dayofyear
// val sql = spark.sql(
// “”"
// |select
// |dayofyear( TXNDATETIME)
// | from XULINV_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//weekofyear

// val sql = spark.sql(
// “”"
// |select
// |weekofyear( ‘2017-10-03’)
// | from XULINV_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//trunc截取某部分的日期，其他部分默认为01
//第二个参数 [“year”, “yyyy”, “yy”, “mon”, “month”, “mm”]

// val sql = spark.sql(
// “”"
// |select
// |trunc(TXNDATETIME,‘year’)
// | from XULINV_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//date_trunc ["YEAR", "YYYY", "YY", "MON", "MONTH", "MM", "DAY", "DD", "HOUR", "MINUTE", "SECOND", "WEEK", "QUARTER"]

// val sql = spark.sql(
// “”"
// |select
// |date_trunc(TXNDATETIME,‘MONTH’)
// | from XULINV_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show() //打印报错

//date_format将时间转化为某种格式的字符串

// val sql = spark.sql(
// “”"
// |select
// |date_format(TXNDATETIME,‘y’)
// | from XULINV_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//1.unix_timestamp返回当前时间的unix时间戳

// val sql = spark.sql(
// “”"
// |select
// |unix_timestamp(TXNDATETIME) as time1
// | from XULINV_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// //2.from_unixtime将时间戳换算成当前时间，to_unix_timestamp将时间转化为时间戳
// val t1 = spark.sql("select from_unixtime(time1) from t1 ").show()

//to_date/date将字符串转化为日期格式，to_timestamp（Since: 2.2.0）

// val sql = spark.sql(
// “”"
// |select
// |to_timestamp(2017-10-03 09:40:06) as time1
// | from XL_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
//
// val t1 = spark.sql("select * from t1 ").show() --报错

//1.months_between两个日期之间的月数

//
// val sql = spark.sql(
// “”"
// |select
// |months_between(TXNDATETIME,REACH_TIME)
// | from XL_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//2. add_months返回日期后n个月后的日期

// val sql = spark.sql(
// “”"
// |select
// |add_months(TXNDATETIME,1)
// | from XL_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//3.last_day(date),next_day(start_date, day_of_week)

// val sql = spark.sql(
// “”"
// |select
// |last_day(TXNDATETIME)
// | from XL_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//4.date_add,date_sub(减)

// val sql = spark.sql(
// “”"
// |select
// |date_sub(TXNDATETIME,1)
// | from XL_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//5.datediff（两个日期间的天数）

// val sql = spark.sql(
// “”"
// |select
// |datediff(TXNDATETIME,‘2017-10-02’)
// | from XL_CARD_INFO
// “”".stripMargin).createOrReplaceTempView(“t1”)
// val t1 = spark.sql("select * from t1 ").show()

//6.关于UTC时间
val sql = spark.sql(
  """
    |select
    |from_utc_timestamp(TXNDATETIME,'Asia/Seoul')
    | from XL_CARD_INFO
  """.stripMargin).createOrReplaceTempView("t1")
val t1 = spark.sql("select * from t1 ").show()







spark.stop()

}