Using Extract in timestamp vs in an interval

When extracting minutes from a timestamp and extracting minutes from an interval, the primary difference lies in the source data and the purpose of the extraction.

1. Extracting Minutes from Timestamp:

   • When you extract minutes directly from a timestamp, you are working with an absolute point in time. The result represents the minute component of that specific timestamp.

   • The timestamp is a specific moment on the timeline, and extracting minutes from it gives you the minutes within the hour for that particular point in time.

Example:

    SELECT EXTRACT(MINUTE FROM '2023-01-01 14:30:00'::TIMESTAMP) AS minutes_from_timestamp;

Result:

    minutes_from_timestamp
    ----------------------
    30

1. Extracting Minutes from Interval:

   • When you extract minutes from an interval, you are working with a duration or difference between two timestamps or time values.

   • The interval represents the time span between two points in time, and extracting minutes from it gives you the duration in minutes.

Example:

    SELECT EXTRACT(MINUTE FROM '2023-01-01 14:30:00'::TIMESTAMP - '2023-01-01 12:45:00'::TIMESTAMP) AS minutes_from_interval;

Result:

    minutes_from_interval
    ----------------------
    105

1. In summary, when you extract minutes from a timestamp, you get the minutes of that specific point in time.

2. When you extract minutes from an interval, you get the duration in minutes between two points in time. The appropriate choice depends on whether you're interested in a specific timestamp or the difference between two timestamps.