But I didn’t understand at the time why this issue would not be dealt with in months and days. Please refer to the following query, which Calculate the precise interval, where the number of seconds between two time points can be accurately calculated:
select ('2017-01-01'::timestamp-'2016-01 -01'::timestamp); -->366 days.
Postgresql chooses to give results within a few days. Not months instead of seconds.
But why The result is days instead of seconds? It does not define the number of days (they can be 23, 24 or 25 hours). Then why doesn’t it output in a few seconds?
Then since the length of the month is not defined, why does postgresql not output 12 months instead of 366 days?
He doesn’t care that the length of the days is not defined, but obviously what he cares about is that the length of the month is not defined.
Why is this asymmetry?
For further instructions, please refer to this query:
select ('10 days'::interval-'24 hours'::interval);- -> 10 days -24:00:00
You see that postgresql correctly refuses to answer for 9 days. He is very aware of the question that daily and hour are not interchangeable. But then it explains why the first query returns How many days?
< /p>
The SQL standard describes two intervals: year-month interval and day-time interval. This is to prevent the month and date parts from appearing in the same time interval, because as you know, the The number of days is not clear. The number of days between the “3” months depends on the three months you are talking about.
I think this is a detailed standard SQL method for writing the first query.
< p>
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 that you will find that the SQL standard has some rules to stipulate what SQL dbms should do, because the interval to one day is omitted. PostgreSQL may Will or may not follow these rules.
postgresql chooses to give a result in days. not in months and not in seconds.
Standard SQL prevents the month and date parts from appearing in the same time interval. In addition, the valid range of 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
But I didn’t understand at the time why this problem would not be dealt with in a few months and days. Please refer to the following query, which calculates the exact interval, two of the time points The number of seconds between can be accurately calculated:
select ('2017-01-01'::timestamp-'2016-01-01'::timestamp); - >366 days.
Postgresql chooses to give results within a few days. Not months instead of seconds.
But why the results are days instead of seconds? It does not define the number of days (they can be 23, 24 or 25 hours). Then why doesn’t it output in a few seconds?
Then since the length of the month is not defined, why does postgresql not output 12 months instead of 366 days?
He doesn’t care that the length of the days is not defined, but obviously what he cares about is that the length of the month is not defined.
Why is this asymmetry?
For further instructions, please refer to this query:
select ('10 days'::interval-'24 hours'::interval);- -> 10 days -24:00:00
You see that postgresql correctly refuses to answer for 9 days. He is very aware of the question that daily and hour are not interchangeable. But then it explains why the first query returns How many days?
I can’t answer your question, but I think I can point you in the right direction. I think SQL-99 is complete, really understand the SQL interval The most accessible source. It can be obtained online: https://mariadb.com/kb/en/sql-99/08-temporal-values/.
The SQL standard describes two Interval: year-month interval and day-time interval. This is to prevent the month and date parts from appearing in the same time interval, because as you know, the number of days in a month is not clear. “3” The number of days between months It depends on the three months you are talking about.
I think this is a detailed standard SQL method for writing the 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 that you will find that the SQL standard has some rules to stipulate what SQL dbms should do, because the interval to one day is omitted. PostgreSQL may or may not follow these rules.
postgresql chooses to give a result in days. not in months and not in seconds.
Standard SQL can prevent month and day parts Appears in the same time interval. In addition, the valid range of 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