Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-26002

SQL date operators calculates with incorrect dayOfYears for dates before 1500-03-01

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.6.3, 2.0.2, 2.1.3, 2.2.0, 2.2.1, 2.2.2, 2.3.0, 2.3.1, 2.3.2, 2.4.0, 3.0.0
    • Fix Version/s: 3.0.0
    • Component/s: SQL
    • Labels:
    • Target Version/s:

      Description

      Running the following SQL the result is incorrect:

      scala> sql("select dayOfYear('1500-01-02')").show()
      +-----------------------------------+
      |dayofyear(CAST(1500-01-02 AS DATE))|
      +-----------------------------------+
      |                                  1|
      +-----------------------------------+
      

      This off by one day is more annoying right at the beginning of a year:

      scala> sql("select year('1500-01-01')").show()
      +------------------------------+
      |year(CAST(1500-01-01 AS DATE))|
      +------------------------------+
      |                          1499|
      +------------------------------+
      
      
      scala> sql("select month('1500-01-01')").show()
      +-------------------------------+
      |month(CAST(1500-01-01 AS DATE))|
      +-------------------------------+
      |                             12|
      +-------------------------------+
      
      
      scala> sql("select dayOfYear('1500-01-01')").show()
      +-----------------------------------+
      |dayofyear(CAST(1500-01-01 AS DATE))|
      +-----------------------------------+
      |                                365|
      +-----------------------------------+
      

       

        Attachments

          Activity

            People

            • Assignee:
              attilapiros Attila Zsolt Piros
              Reporter:
              attilapiros Attila Zsolt Piros
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: