PySpark如何从年月/年季数值列生成时间戳列？

环境版本 #

• PySpark 3.4.1
• Java 1.8.0_372

已创建的DataFrame #

from pyspark import SparkContext
sc = SparkContext.getOrCreate()
from pyspark.sql import SparkSession
spark_session = SparkSession(sc)
mydf = spark_session.createDataFrame(
    data=[(2018, 1), (2019, 4), (2018, 3), (2019, 4), (2018, 2), (2020, 1), (2020, 4)],
    schema=['myYear', 'myMonth']
)
mydf.show()

输出：

+------+-------+
|myYear|myMonth|
+------+-------+
|  2018|      1|
|  2019|      4|
|  2018|      3|
|  2019|      4|
|  2018|      2|
|  2020|      1|
|  2020|      4|
+------+-------+

一、从年月列生成时间戳列 #

之前报错的核心原因是：单个数字的月份（如1）转字符串后是"1"，拼接年份后得到"20181"，不符合yyyyMM格式要求（月份必须是两位）。解决时需要先把月份格式化为两位字符串，再拼接转换。

实现代码 #

from pyspark.sql.functions import lpad, concat, to_timestamp

# 生成year-month时间戳列
mydf_with_month_ts = mydf.withColumn(
    "year-month",
    to_timestamp(concat(mydf.myYear.cast("string"), lpad(mydf.myMonth.cast("string"), 2, "0")), "yyyyMM")
)

# 查看结果
mydf_with_month_ts.show()
mydf_with_month_ts.printSchema()

输出结果 #

+------+-------+-------------------+
|myYear|myMonth|         year-month|
+------+-------+-------------------+
|  2018|      1|2018-01-01 00:00:00|
|  2019|      4|2019-04-01 00:00:00|
|  2018|      3|2018-03-01 00:00:00|
|  2019|      4|2019-04-01 00:00:00|
|  2018|      2|2018-02-01 00:00:00|
|  2020|      1|2020-01-01 00:00:00|
|  2020|      4|2020-04-01 00:00:00|
+------+-------+-------------------+

Schema：

root
 |-- myYear: long (nullable = true)
 |-- myMonth: long (nullable = true)
 |-- year-month: timestamp (nullable = true)

如果需要date类型而非timestamp，只需把to_timestamp替换为to_date即可。

二、从年季度列生成时间戳列 #

假设已有包含myYear（年份）和myQuarter（1-4的整数）的DataFrame，需要生成对应季度第一天的时间戳。核心思路是先把季度转换为对应季度的第一个月份（Q1→1，Q2→4，Q3→7，Q4→10），再复用上面的年月转换逻辑。

构造带季度的DataFrame #

df_with_quarter = spark_session.createDataFrame(
    data=[(2018, 1), (2019, 2), (2018, 3), (2019, 4), (2020, 1)],
    schema=['myYear', 'myQuarter']
)
df_with_quarter.show()

输出：

+------+----------+
|myYear|myQuarter|
+------+----------+
|  2018|         1|
|  2019|         2|
|  2018|         3|
|  2019|         4|
|  2020|         1|
+------+----------+

实现代码 #

from pyspark.sql.functions import expr, to_timestamp

# 生成year-quarter时间戳列
df_with_quarter_ts = df_with_quarter.withColumn(
    "year-quarter",
    to_timestamp(expr("concat(myYear, lpad((myQuarter-1)*3 +1, 2, '0'))"), "yyyyMM")
)

# 查看结果
df_with_quarter_ts.show()
df_with_quarter_ts.printSchema()

输出结果 #

+------+----------+-------------------+
|myYear|myQuarter|      year-quarter|
+------+----------+-------------------+
|  2018|         1|2018-01-01 00:00:00|
|  2019|         2|2019-04-01 00:00:00|
|  2018|         3|2018-07-01 00:00:00|
|  2019|         4|2019-10-01 00:00:00|
|  2020|         1|2020-01-01 00:00:00|
+------+----------+-------------------+

Schema：

root
 |-- myYear: long (nullable = true)
 |-- myQuarter: long (nullable = true)
 |-- year-quarter: timestamp (nullable = true)

内容的提问来源于stack exchange，提问作者Naveen Reddy Marthala