0

I have a query with a column that converts the universal datetime field (not a timestamp) to a local time based on the the timezone. In Oracle I was able to do this with intervals like this snippet below, but Spark wouldn't allow intervals. How can I do this in Spark.SQL?

case when c.timezone in (4,5) then to_char(b.universal_datetime + 

NUMTODSINTERVAL(3, 'HOUR'),'yyyy/mm/dd HH24:MI:SS')
when c.timezone in (8) then to_char(b.universal_datetime,'yyyy/mm/dd HH24:MI:SS')
when c.timezone in (7) then to_char(b.universal_datetime + NUMTODSINTERVAL(1, 'HOUR'),'yyyy/mm/dd HH24:MI:SS')
when c.timezone in (6) then to_char(b.universal_datetime + NUMTODSINTERVAL(2, 'HOUR'),'yyyy/mm/dd HH24:MI:SS')
when c.timezone in (10) then to_char(b.universal_datetime - NUMTODSINTERVAL(3, 'HOUR'),'yyyy/mm/dd HH24:MI:SS')
when c.timezone in (9) then to_char(b.universal_datetime - NUMTODSINTERVAL(1, 'HOUR'),'yyyy/mm/dd HH24:MI:SS')
ELSE 'Other' END AS Local_Time,
infamoustrey
  • 730
  • 8
  • 16
MDM82
  • 71
  • 3

1 Answers1

1
SELECT 
  current_timestamp() AS current_timestamp,
 (current_timestamp() - INTERVAL '6' HOUR) AS current_timestamp_minus_six_hours

Suprised this isn't part of the apache spark sql built-in time functions. https://spark.apache.org/docs/2.3.0/api/sql/index.html

Brian Vernon
  • 11
  • 1
  • What is unix_timestamp(), used for? Well there is no built-in function but you don't need to write a SQL to achieve this. Thanks to @psidom https://stackoverflow.com/questions/47897238/add-extra-hours-to-timestamp-columns-in-pyspark-data-frame?rq=1 – pvy4917 Oct 29 '18 at 18:02
  • 1
    Interesting, but @psidom solution uses pyspark and not spark sql. Also, you have to calculate the interval down to a second instead of using the interval you want (day/month/minute/second). I would be interested if there was a simpler version of converting the current dttm to and from UTC instead of having to write custom functions. – Brian Vernon Oct 30 '18 at 20:42