Forum Discussion

pmscorca's avatar
pmscorca
Kudo Kingpin
1 year ago
Solved

How calculating the difference between two datetimes in minutes in pyspark SQL

Hi, in a my lakehouse table I've a timestamp or datetime data. I need to calculate the difference between this timestamp and the current timestamp, in minutes. I can determine the current timestam...
data engineering
notebook
  • pmscorca's avatar
    pmscorca
    1 year ago

    Hi, thanks for your reply.

    I've solved with:

    sql_sample_table = """
SELECT * FROM mytable
WHERE cast(((unix_timestamp()-unix_timestamp(mytimestamp)) / 60) as int) > 30
"""
spark.sql(sql_sample_table).show()

    unix_timestamp() returns the current timestamp in seconds.

    The unix_timestamp statement in Spark SQL returns the number of seconds that have passed since January 1, 1970 (epoch time) until the specified date and time.

    Thanks

frithjof_v's avatar
frithjof_v
Community Champion
1 year ago

Chat GPT suggests the following, does it work in your case?

 

sql_sample_table = """

SELECT * 

FROM mytable 

WHERE (unix_timestamp(current_timestamp()) - unix_timestamp(mytimestamp)) > 30 * 60

"""

 

spark.sql(sql_sample_table).show()

  • pmscorca's avatar
    pmscorca
    Kudo Kingpin
    1 year ago

    Hi, thanks for your reply.

    I've solved with:

    sql_sample_table = """
SELECT * FROM mytable
WHERE cast(((unix_timestamp()-unix_timestamp(mytimestamp)) / 60) as int) > 30
"""
spark.sql(sql_sample_table).show()

    unix_timestamp() returns the current timestamp in seconds.

    The unix_timestamp statement in Spark SQL returns the number of seconds that have passed since January 1, 1970 (epoch time) until the specified date and time.

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      HI pmscorca,

      I'm glad to hear you get the resolution and share it here, they will help others who has the similar requirement.

      Regards,

      Xiaoxin Sheng