如何在Spark SQL中格式化日期？将指定日期转为UTC标准格式

Got it, let's sort out this date formatting problem properly. Your current concat approach is causing redundant time parts because you're just tacking on the string suffix directly to the full timestamp string—we can use Spark SQL's built-in date/time functions to do this cleanly and correctly.

Here are a couple of reliable methods depending on your data type:

1. If your VERSION_TIME is already a Timestamp type #

Use the date_format function to directly format the timestamp into the ISO 8601 format you need, including the T separator, millisecond precision, and Z (UTC indicator):

SELECT date_format(d2.VERSION_TIME, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") AS VERSION_TIME

• The format string yyyy-MM-dd'T'HH:mm:ss.SSS'Z' explicitly defines the structure:
  • yyyy-MM-dd: Date part (year-month-day)
  • 'T': Fixed literal separator between date and time
  • HH:mm:ss.SSS: Time part with hour, minute, second, and 3-digit milliseconds
  • 'Z': Fixed literal for UTC timezone indicator

2. If your VERSION_TIME is a String type #

First convert the string to a timestamp using to_timestamp, then apply the same date_format function:

SELECT date_format(
    to_timestamp(d2.VERSION_TIME, "yyyy-MM-dd HH:mm:ss"), 
    "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
) AS VERSION_TIME

• The to_timestamp function parses your original string (2019-10-22 00:00:00) into a proper timestamp value, which we then format correctly.

Bonus: Ensuring UTC Timezone #

If your original timestamp is in a different timezone and you need to ensure the output is UTC, use to_utc_timestamp before formatting:

SELECT date_format(
    to_utc_timestamp(d2.VERSION_TIME, 'UTC'), 
    "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
) AS VERSION_TIME

This approach is way more robust than string concatenation—it handles edge cases (like non-midnight times) correctly, keeps your code clean, and produces the exact standardized format you're after.

内容的提问来源于stack exchange，提问作者Fisher Coder