pyspark date_format function returns incorrect year

user_5462467 0
Viewed 1302

pyspark.sql.functions.date_format - returns incorrect year for last day of the year

Pyspark Version: version 2.3.0.cloudera3 Python version : Python 2.7.5

When I try to reformat last date of the year using the function date_format in pyspark, it returns the next year when used with "YYYY" instead of "yyyy". 

>>> from pyspark.sql.functions import *

>>> dftest = spark.createDataFrame([('2017-12-31',)], ['dt'])

>>> dftest.select(date_format('dt', 'MM/dd/yyy').alias('date')).collect()
[Row(date=u'12/31/2017')]
>>> dftest.select(date_format('dt', 'MM/dd/yyyy').alias('date')).collect()
[Row(date=u'12/31/2017')]

>>> dftest.select(date_format('dt', 'MM/dd/YYY').alias('date')).collect()
[Row(date=u'12/31/2018')]
>>> dftest.select(date_format('dt', 'MM/dd/YYYY').alias('date')).collect()
[Row(date=u'12/31/2018')]

How is "YYYY" (upper case) different from "yyyy" (lower case)?

pyspark date-format python
1 Answers

According to the documentation on date_format:

"All pattern letters of the Java class java.text.SimpleDateFormat can be used"

and if you look in the documentation on java e.g. java.text.SimpleDateFormat, you can see that uppercase Y refers to the week year and not the year itself as lowercase y.

With more years than in your example:

dftest = spark.createDataFrame([('20{}-12-31'.format(i),) for i in range(19, 25)], ['dt'])
dftest.select('dt', date_format('dt', 'MM/dd/yyyy').alias('date'),
                    date_format('dt', 'MM/dd/YYYY').alias('DATE'),).show()
+----------+----------+----------+
|        dt|      date|      DATE|
+----------+----------+----------+
|2019-12-31|12/31/2019|12/31/2020|
|2020-12-31|12/31/2020|12/31/2021|
|2021-12-31|12/31/2021|12/31/2022|
|2022-12-31|12/31/2022|12/31/2022| # this one is good for both
|2023-12-31|12/31/2023|12/31/2024|
|2024-12-31|12/31/2024|12/31/2025|
+----------+----------+----------+

You can see that 2022 is good, and the year ends on a Saturday. If you try more years, you will find the same result when they end on a Saturday. So my guess (not fully sure) is when you use uppercase Y, the parsing goes to the following Saturday and get the year associated to this Saturday. For 2019, as it is a Tuesday, it jump to Saturday 4th of January 2020 and get this year instead of 2019.

user_92747320
Related