Inconsistency between month, day, second representation of interval data type
I understand why postgresql uses month, day and second fields to represent the sql interval data type. The month is not always the same length, and there can be 23, 24, or 25 hours per day if daylight saving time is enabled. this is from postgresql documentation.
But I then don't understand why it is therefore not handled as months but days. see the following query which calculates the exact interval, where the number of seconds between two points in time is exactly calculated:
select ('2017-01-01'::timestamp-'2016-01-01'::timestamp); -->366 days.
postgresql fetches the result in days. not in months, not in seconds.
But why is it the result of days and not seconds? it is NOT defined how long it lasts (they can be 23.24 or 25 hours). so why doesn't it give output in seconds?
Then, since the length of months is also undefined, why doesn't postgresql return 12 months instead of 366 days?
He doesn't care that the length of the days is not determined, but obviously he takes care that the length of the month is not determined.
Why is this asymmetry?
See this question for more details:
select ('10 days'::interval-'24 hours'::interval); --> 10 days -24:00:00
you see that postgresql correctly refuses to respond with 9 days. He perfectly understands the problem that days and hours cannot be interchangeable. But then again, why does the first query return days?
source to share
I cannot answer your question, but I think I can point you in the right direction. I think the book SQL-99 Complete is indeed the most readily available source for understanding SQL intervals. It is available online: https://mariadb.com/kb/en/sql-99/08-temporal-values/ .
The SQL standards describe two types of intervals: intervals between months and daily intervals. It does this to prevent parts of the month and day of the month from appearing in the same interval, because, as you already know, the number of days in a month is ambiguous. The number of days in interval '3' month
depends on the three months you are talking about.
I think this is the tricky standard SQL way to write your first query.
select cast(timestamp '2017-01-01' - timestamp '2016-01-01' as interval day to hour) as new_column;
new_column
interval day to hour
--
366 days
I suspect you will find that the SQL standards have rules for what SQL-dbms should do when things like interval day to hour
are omitted. PostgreSQL may or may not follow these guidelines.
postgresql fetches the result in days. not in months, not in seconds.
Standard SQL prevents month and day parts from appearing in the same interval. In addition, the range of valid seconds is 0 to 59.
select interval '59' second;
interval
interval second
--
00:00:59
select interval '60' second;
interval
interval second
--
00:01:00
source to share