what would be the outputs of these SQL functions-CodePudding

SELECT EXTRACT(d from DATE_TRUNC('month', '2022-10-30'::DATE))

and

SELECT EXTRACT(month from '2022-10-30'::DATE   interval '5 days')

I thought it was going to be 30 for the first one because it says "D" before date trunc, but that is wrong.

For the second one, I am confused and I wasn't able to find any helpful tips on the internet nor was my book helpful . I am not understanding how interval 5 days can affect the month. If you could help me with this, I would really appreciate it!

CodePudding user response：

The first query will return 1 as you are using DATE_TRUNC with field 'month' and datepart is DATE so it will return

SELECT DATE_TRUNC('month', '2022-10-30'::DATE)

will return 2022-10-01T00:00:00Z and when you extract the d then it will return 1.

For the second case when you are adding 5 days to the date then the date is changing to 2022-11-04T00:00:00Z hence the month is changing to 11 instead of 10 hence the result will be 11.