TIMESTAMP appears to add an hour compared to DATETIME

[Naboo22]

Env:

• PySpark Version: 3.5.1
• spark.jars.packages: com.google.cloud.spark:spark-3.5-bigquery:0.38.0

---

The following code

# Define task attempts SQL
sql = f"""
SELECT 
datetime("2023-09-18 09:54:12.442227") as datetime,
timestamp("2023-09-18 09:54:12.442227") as timestamp
"""

# Load data from BigQuery.
test = spark.read.format("bigquery").load(
    sql,
)

produces the resultant table:

+--------------------+--------------------+ | datetime| timestamp| +--------------------+--------------------+ |2023-09-18 09:54:...|2023-09-18 10:54:...| +--------------------+--------------------+

You can see that the timestamp field is an hour ahead of the datetime field. I am unclear if this is intended behaviour to do with timezones, but the same query run in the BigQuery console yields identical times.

[davidrabinowitz]

Notice that DATETIME and TIMESTAMP are not identical:

• DATETIME - A datetime value represents a Gregorian date and a time, as they might be displayed on a watch, independent of time zone. It includes the year, month, day, hour, minute, second, and subsecond. It is represented in your local time zone.
• [TIMESTAMP}(https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp_type) - A timestamp value represents an absolute point in time, independent of any time zone or convention such as daylight saving time (DST), with microsecond precision. It is reparented in UTC, which is not affected by the daylight saving time.

The documentation go on and say

To represent an absolute point in time, use a timestamp.

You can see the results of this query, when I'm at the America/Los_Angeles time zone, notice the difference in the values.

[Naboo22]

@davidrabinowitz thanks, I think I've wrapped my head around that now!