Forum Discussion
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 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)
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
3 Replies
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()
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
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
