casting (converting) integer into interval in PostgreSQL

Question

If I execute query: SELECT '10' AS a_name; it gives 10 AS text type. In case of query: SELECT 10::text AS a_name; it gives again 10 as text. So one could expect that following two queries gave the same result:

SELECT '10'::interval day;
SELECT 10::text::interval day;

Nevertheless the first query gives 10 days and the second gives 00:00:00. both type of interval. Please, explain why SELECT '10' and SELECT 10::text are interpreted in different way even they both give the same tape and values, or where is my understanding mistaken.

Actually it does not: 1) SELECT pg_typeof('10') as a_name; unknown. 2) SELECT pg_typeof('10'::text) as a_name; text. What I have not figured out is why that makes a difference? 3) As a work around SELECT (10::text || ' day')::interval; 10 days. — Adrian Klaver
– Adrian Klaver, Commented Sep 4, 2024 at 15:31

JohnH · Accepted Answer · 2024-09-04 17:19:16Z

2

The difference in behavior is because the two expressions, '10' and '10'::TEXT are not semantically equivalent within the database. In the expression '10'::INTERVAL DAY, '10' is a symbol of unknown type which is then cast to INTERVAL DAY. In the expression '10'::TEXT::INTERVAL DAY, '10' is again a symbol of unknown type, which is cast to TEXT before being cast to INTERVAL DAY. The reason '10' shows in the client as having type TEXT is because of an implicit cast from unknown type to TEXT when the results are returned to the client.

answered Sep 4, 2024 at 17:19

JohnH

3,2932 gold badges6 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zegarek Over a year ago

This doesn't answer the question and the description of the behaviour isn't accurate. 4.1.2.7 Constants Of Other Types and 4.2.9 Type Casts explain '10'::interval is a direct assignment, entirely bypassing a cast. As already pointed out by @Adrian Klaver, a bare '10' is unknown, not text. The only way you'd get a text is if your client was configured to quietly add ::text.

JohnH Over a year ago

@Zegarek, the OP asked why '10' and '10'::TEXT behave differently when cast to INTERVAL and this is the question that I addressed. My goal was to provide a conceptual model of the behavior as an aid to understanding, not a detailed description of specific mechanisms. I intentionally used cast in its abstract sense, not as a CAST operator. Although SELECT pg_typeof('10') returns unknown, pgAdmin reports SELECT '10' as having type text; thus, there is an implicit type conversion between the server and the client.

Zegarek · Accepted Answer · 2024-09-25 11:58:53Z

TL;DR: To be safe, use make_interval(days=>10).

The first example is an assignment. '10' is an unknown-type constant. When you assign interval type to it, Postgres assumes it's '10 seconds':

explain verbose SELECT '10'::interval;


Output: '00:00:10'::interval

The day is normally only a field restriction type modifier but in an assignment context, it also changes the assumption about units and explain can show you that it's altering the constant to '10 days':

explain verbose SELECT '10'::interval day;


Output: '10 days'::interval day

Unfortunately, the doc doesn't describe the second part of that mechanism:

The interval type has an additional option, which is to restrict the set of stored fields by writing one of these phrases:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

The reason it does that is likely the principle of least astonishment - it's not unreasonable to expect 10 days when you type interval day '10' or '10'::interval day.

The logic is in postgres/src/backend/utils/adt /datetime.c, inside DecodeInterval(): it goes through your literal and "reads through list backwards to pick up units before values", checks for time and timezone and if it finds nothing of that sort, it falls through dates to plain numbers, where it decides to use the type modifier to establish the unit:

/* use typmod to decide what rightmost field is */
switch (range)
{
    case INTERVAL_MASK(YEAR):
        type = DTK_YEAR;
        break;
    case INTERVAL_MASK(MONTH):
    case INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH):
        type = DTK_MONTH;
        break;
    case INTERVAL_MASK(DAY):
        type = DTK_DAY;
        break;
    case INTERVAL_MASK(HOUR):
    case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR):
        type = DTK_HOUR;
        break;
    case INTERVAL_MASK(MINUTE):
    case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
    case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE):
        type = DTK_MINUTE;
        break;
    case INTERVAL_MASK(SECOND):
    case INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
    case INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
    case INTERVAL_MASK(DAY) | INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND):
        type = DTK_SECOND;
        break;
    default:
        type = DTK_SECOND;
        break;
}

At the end you can see it defaulting to seconds.

SELECT 10::text::interval day;

The second example involves one assignment and one cast. That whole guesswork that applies in the assignment context, still kicks in, but now it sees a 10 that wants to become text, so no additional steps are taken: (10::unknown)::text → '10'::text

On the second cast of what's already a textual '10' to an interval, the day restriction does not affect the constant - it's been pre-processed already and at that stage no further guesswork trickery takes place. As a result it becomes a 00:00:10 which is then subjected to the day restriction, so to only keep whole days, the 10 seconds get truncated away, leaving you with 00:00:00.

All constants start off as ::unknown but only the first type assignment from that is an assignment where this sort of thing can happen, the rest is a cast that works differently. Unfortunately, you can't go back and re-do the assignment:
_{demo at db<>fiddle}

select '10'::unknown--a no-op, it's implied
           ::text   --an assignment, because it's the first known type
           ::unknown--does not go back to unassigned unknown, just a weird cast
                    --allowed pretty much only if the re-writer can reduce it
           ::interval day--won't work now in the cast context from unknown

Collectives™ on Stack Overflow

casting (converting) integer into interval in PostgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related