@Erik Louie​ :

If the data frames have different time zones, you can use Databricks' timezone conversion function to convert them to a common time zone. You can use the from_utc_timestamp or to_utc_timestamp

function to convert the timestamp column to a UTC timestamp, and then use the date_format function to convert it to a timestamp string in a particular time zone.

For example, if your data frames have timestamps in different time zones, you can use the following code to convert them to a common time zone:

from pyspark.sql.functions import from_utc_timestamp, to_utc_timestamp, date_format
 
# Convert the timestamp column to a UTC timestamp
df_1_utc = df_1.withColumn('timestamp_utc', from_utc_timestamp(df_1.timestamp, df_1.timezone))
df_2_utc = df_2.withColumn('timestamp_utc', from_utc_timestamp(df_2.timestamp, df_2.timezone))
 
# Convert to a timestamp string in a particular time zone
df_1_localized = df_1_utc.withColumn('timestamp_local', date_format(to_utc_timestamp(df_1_utc.timestamp_utc, 'America/Los_Angeles'), 'yyyy-MM-dd HH:mm:ss'))
df_2_localized = df_2_utc.withColumn('timestamp_local', date_format(to_utc_timestamp(df_2_utc.timestamp_utc, 'America/Los_Angeles'), 'yyyy-MM-dd HH:mm:ss'))
 
# Join the data frames on the localized timestamp column
df_all = (
    df_1_localized.join(df_2_localized, on=['timestamp_local', 'join_idx'], how="inner")
    .join(df_3, on=['timestamp_local', 'join_idx'], how="inner")
    .join(df_4, on=['timestamp_local', 'join_idx'], how="inner")
    .withColumn("id", lit(id))
).writeTo("joined_table").createOrReplace()

This code converts the timestamp column to a UTC timestamp using from_utc_timestamp, and then converts it to a localized timestamp string using to_utc_timestamp and date_format. Finally, it joins the data frames on the localized timestamp column.