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

[pmscorca]

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 timestamp using current_timestamp().

How could I calculate the difference between the current timestamp and the timestamp in the table, expressed in minutes, using pyspark SQL, please? Thanks

I think to a code similar to this:

sql_sample_table = """
SELECT * FROM mytable WHERE current_timestamp()-mytimestamp > 30"""
spark.sql(sql_sample_table)

[frithjof_v]

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 · Accepted solution]

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

[v-shex-msft]

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