将12小时添加到Spark中的datetime列

问题描述

我已经尝试了很多搜索，但是只能在Spark SQL中找到add_month函数，因此最终在这里打开了一个新线程.希望有人能提供任何帮助.

I have tried to search quite a bit, but could only found add_month function in Spark SQL, so ending up opening a new thread here. Would appreciate any help someone could offer.

我试图使用sqlContext将小时12、24和48添加到Spark SQL中的日期列.我正在使用1.6.1版本的Spark，我需要这样的东西:

I am trying to add hours 12, 24, and 48 to a date column in Spark SQL using sqlContext. I am using 1.6.1 version of Spark and I need something like this:

SELECT N1.subject_id, '12-HOUR' AS notes_period, N1.chartdate_start, N2.chartdate, N2.text
FROM NOTEEVENTS N2,
(SELECT subject_id, MIN(chartdate) chartdate_start
  FROM NOTEEVENTS
  WHERE subject_id = 283
  AND category != 'Discharge summary'
GROUP BY subject_id) N1
WHERE N2.subject_id = N1.subject_id
and n2.chartdate < n1.chartdate_start + interval '1 hour' * 12

请注意最后一个子句，它是用PostgreSql编写的，这是我在Spark SQL中所需要的.我非常感谢我能得到的任何帮助.

Please notice the last clause, which is written in PostgreSql, and is what I need in Spark SQL. I'd really appreciate any help I could get.

谢谢.

推荐答案

当前没有此类功能，但是您可以编写UDF:

Currently there's no such function, but you can write UDF:

sqlContext.udf.register("add_hours", (datetime : Timestamp, hours : Int) => {
    new Timestamp(datetime.getTime() + hours * 60 * 60 * 1000 )
});

例如:

SELECT N1.subject_id, '12-HOUR' AS notes_period, N1.chartdate_start, N2.chartdate, N2.text
    FROM NOTEEVENTS N2,
    (SELECT subject_id, MIN(chartdate) chartdate_start
      FROM NOTEEVENTS
      WHERE subject_id = 283
      AND category != 'Discharge summary'
    GROUP BY subject_id) N1
    WHERE N2.subject_id = N1.subject_id
    and n2.chartdate < add_hours(n1.chartdate_start, 12)

您还可以使用unix_timestamp函数来计算新日期.我认为它的可读性较差，但可以使用WholeStage Code Gen.受Anton Okolnychyi其他答案启发的代码

You can also use unix_timestamp function to calculate new date. It's less readable in my opinion, but can use WholeStage Code Gen. Code inspired by Anton Okolnychyi other answer

import org.apache.spark.sql.functions._
val addMonths = (datetime : Column, hours : Column) => {
     from_unixtime(unix_timestamp(n1.chartdate_start) + 12 * 60 * 60)
}

这篇关于将12小时添加到Spark中的datetime列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！