Pyspark: Difference between two Dates (Cast TimestampType, Datediff)

timestamp pyspark datediff
Niklas Brauer picture Niklas Brauer · Jun 29, 2017 · Viewed 20.8k times · Source

There is a table with incidents and a specific timestamp. I struggle to calculate the number of days passed using the Pyspark 2.0 API. I managed to do the same thing when the timestamp followed another format (yyyy-mm-dd)

    +-------------------+------------------------+----------+--------------+
     | first_booking_date|first_booking_date_clean|  today   |customer_since|
    +-------------------+------------------------+----------+--------------+
     |02-06-2011 20:52:04|              02-06-2011|02-06-2011|          null|
     |03-06-2004 18:15:10|              03-06-2004|02-06-2011|          null|

I tried the following (nothing worked): - extract date with string manipulation and use datediff - cast to timestamp and then extract dd:MM:yy (->result null) - I prefer to use pyspark commands over any additional transformation with sql

Help is highly appreciated, Best and thanks a lot!!!

EDIT: Here is an example that did not work:

import datetime
today = datetime.date(2011,2,1)
today = "02-06-2011"
first_bookings = first_bookings.withColumn("today",F.lit(today))
first_bookings = first_bookings.withColumn("first_booking_date_clean",F.substring(first_bookings.first_booking_date, 0, 10))
first_bookings = first_bookings.withColumn("customer_since",F.datediff(first_bookings.today,first_bookings.first_booking_date_clean))

Answer

Zephro picture Zephro · Jul 1, 2017

This answer is basically a copy of https://stackoverflow.com/a/36985244/4219202 In your case the timeFmt would be "dd-MM-yyyy" for the columns first_booking_date_clean and today

As of Spark 1.5 you can use unix_timestamp:

from pyspark.sql import functions as F
timeFmt = "yyyy-MM-dd'T'HH:mm:ss.SSS"
timeDiff = (F.unix_timestamp('EndDateTime', format=timeFmt)
            - F.unix_timestamp('StartDateTime', format=timeFmt))
df = df.withColumn("Duration", timeDiff)

Related questions

Spark DataFrame TimestampType - how to get Year, Month, Day values from field?

I have Spark DataFrame with take(5) top rows as follows: [Row(date=datetime.datetime(1984, 1, 1, 0, 0), hour=1, value=638.55), Row(date=datetime.datetime(1984, 1, 1, 0, 0), hour=2, value=638.55), Row(date=datetime.datetime(1984, 1, 1, 0, 0), hour=3, value=638.55), Row(date=datetime.datetime(1984, 1, 1, 0, 0), hour=4, value=638.55), Row(date=datetime.datetime(1984, 1, 1, 0, 0), hour=5, value=638.55)] …

Read More
Spark: converting GMT time stamps to Eastern taking daylight savings into account

I'm trying to convert a column of GMT timestamp strings into a column of timestamps in Eastern timezone. I want to take daylight savings into account. My column of timestamp strings look like this: '2017-02-01T10:15:21+00:00' I …

Read More
How do you get a timestamp in JavaScript?

How can I get a timestamp in JavaScript? Something similar to Unix timestamp, that is, a single number that represents the current time and date. Either as a number or a string.

Read More