PostgreSQL – Unexpected Behavior for to_date() with Week Number and Week Day

date formatpostgresql

I tried to convert a date string (year, week, day of week = 'YYYY-WW-D') into a date using the to_date() function.

I got these results (demo:db<>fiddle):

date string |  to_date()
-------------------------
'2019-1-1'  |  2019-01-01
'2019-1-2'  |  2019-01-01
'2019-1-3'  |  2019-01-01
'2019-1-4'  |  2019-01-01
'2019-1-5'  |  2019-01-01
'2019-1-6'  |  2019-01-01
'2019-1-7'  |  2019-01-01

'2019-2-1'  |  2019-01-08
'2019-2-2'  |  2019-01-08

It is not clear to me, why this results in the same date for every day of a week.

If I take the ISO format instead ('IYYY-IW-ID') this works as expected (demo:db<>fiddle):

date string |  to_date()
-------------------------
'2019-1-1'  |  2018-12-31
'2019-1-2'  |  2019-01-01
'2019-1-3'  |  2019-01-02
'2019-1-4'  |  2019-01-03
'2019-1-5'  |  2019-01-04
'2019-1-6'  |  2019-01-05
'2019-1-7'  |  2019-01-06

'2019-2-1'  |  2019-01-07
'2019-2-2'  |  2019-01-08

Why does it work for the ISO pattern, not for the standard pattern? What am I missing here?

Best Answer

This behavior is documented:

In to_timestamp and to_date, weekday names or numbers (DAY, D, and related field types) are accepted but are ignored for purposes of computing the result. The same is true for quarter (Q) fields.

Related Solutions

How to guess or recover a timestamp conversion in sqllite

The Unix epoch timestamp is defined as the number of seconds since the beginning of 1970 (in UTC).

These timestamps are measured from the beginning of 2001, and appear to use the respective local time zone:

> select datetime(strftime('%s', '2013-11-10') - 405720000, 'unixepoch');
2001-01-01 04:00:00
> select strftime('%s', '2013-11-10') - strftime('%s', '2001-01-01 04:00:00');
405720000

To correct for the random time zone differences, you should round to the nearest midnight.

Database Agnostic – How to Convert Date Range to Interval Description

The following solution is for SQL Server. The approach is similar to Serg's in that the query uses only the DATEADD and DATEDIFF functions. It does not, however, account for negative intervals (FromDate > ToDate), and it derives years and months from the total month difference:

WITH
  MonthDiff AS
  (
    SELECT
      t.FromDate,
      t.ToDate,
      t.ExpectedResult,
      Months = x.Months - CASE WHEN DAY(t.FromDate) > DAY(t.ToDate) THEN 1 ELSE 0 END
    FROM
      dbo.TestData AS t
      CROSS APPLY (SELECT DATEDIFF(MONTH, t.FromDate, t.ToDate)) AS x (Months)
  )
SELECT
  t.FromDate,
  t.ToDate,
  t.ExpectedResult,
  Result = ISNULL(NULLIF(ISNULL(x.Years  + CASE x.Years  WHEN '1' THEN ' year '  ELSE ' years '  END, '')
                       + ISNULL(x.Months + CASE x.Months WHEN '1' THEN ' month ' ELSE ' months ' END, '')
                       + ISNULL(x.Days   + CASE x.Days   WHEN '1' THEN ' day '   ELSE ' days '   END, ''), ''), '0 days')
FROM
  MonthDiff AS t
  CROSS APPLY
  (
    SELECT
      CAST(NULLIF(t.Months / 12, 0) AS varchar(10)),
      CAST(NULLIF(t.Months % 12, 0) AS varchar(10)),
      CAST(NULLIF(DATEDIFF(DAY, DATEADD(MONTH, t.Months, t.FromDate), t.ToDate), 0) AS varchar(10))
  ) AS x (Years, Months, Days)
;

Output:

FromDate    ToDate      ExpectedResult                 Result
----------  ----------  -----------------------------  -----------------------------
1999-12-31  1999-12-31  0 days                         0 days
1999-12-31  2000-01-01  1 day                          1 day 
2000-01-01  2000-02-01  1 month                        1 month 
2000-02-01  2000-03-01  1 month                        1 month 
2000-01-28  2000-02-29  1 month, 1 day                 1 month 1 day 
2000-01-01  2000-12-31  11 months, 30 days             11 months 30 days 
2000-02-28  2000-03-01  2 days                         2 days 
2001-02-28  2001-03-01  1 day                          1 day 
2000-01-01  2001-01-01  1 year                         1 year 
2000-01-01  2011-01-01  11 years                       11 years 
9999-12-30  9999-12-31  1 day                          1 day 
1900-01-01  9999-12-31  8099 years 11 months 30 days   8099 years 11 months 30 days

Best Answer

Related Solutions

How to guess or recover a timestamp conversion in sqllite

Database Agnostic – How to Convert Date Range to Interval Description

Related Question