Extracting the Total Number of Seconds from an Interval Data-Type

Extracting the total number of seconds from an interval data-type

I hope this help:

zep@dev> select interval_difference
      2        ,sysdate + (interval_difference * 86400) - sysdate as fract_sec_difference
      3  from   (select systimestamp - (systimestamp - 1) as interval_difference
      4          from   dual)
      5 ;

INTERVAL_DIFFERENCE                                                             FRACT_SEC_DIFFERENCE
------------------------------------------------------------------------------- --------------------
+000000001 00:00:00.375000                                                                 86400,375

With your test:

zep@dev> select interval_difference
      2        ,abs(extract(second from interval_difference) +
      3        extract(minute from interval_difference) * 60 +
      4        extract(hour from interval_difference) * 60 * 60 +
      5        extract(day from interval_difference) * 60 * 60 * 24) as your_sec_difference
      6        ,sysdate + (interval_difference * 86400) - sysdate as fract_sec_difference
      7        ,round(sysdate + (interval_difference * 86400) - sysdate) as sec_difference
      8        ,round((sysdate + (interval_difference * 86400) - sysdate) * 1000) as millisec_difference
      9  from   (select systimestamp - (systimestamp - 1) as interval_difference
     10          from   dual)
     11  /

INTERVAL_DIFFERENCE                                                             YOUR_SEC_DIFFERENCE FRACT_SEC_DIFFERENCE SEC_DIFFERENCE MILLISEC_DIFFERENCE
------------------------------------------------------------------------------- ------------------- -------------------- -------------- -------------------
+000000001 00:00:00.515000                                                                86400,515            86400,515          86401            86400515

zep@dev> 

Converting PostgreSQL interval to seconds produces wrong values

Your problem is integer arithmetic. The mistake is here:

select extract ('epoch' from '1 year'::INTERVAL);

Produces the number 31557600. If we divide this number by (60*60*24) which is the number of seconds in a day, we get 365.

What you probably did was

SELECT 31557600 / (60 * 60 * 24);

 ?column? 
----------
      365
(1 row)

But the reality is:

SELECT extract (epoch FROM INTERVAL '1 year') / (60 * 60 * 24);

 ?column? 
----------
   365.25
(1 row)

So the length of a year in PostgreSQL is actually 365 days and 6 hours. This is an approximation (the true value is slightly less) and should account for leap years.

---

Note: These values are kind of arbitrary because there is no single correct answer for “how long is one (calendar) month” or “how long is one (calendar) year” — the answer depends on the individual month or year.

If you add an interval to a timestamp with time zone, the result will always be correct, because in that context the exact length is clear.

---

As to the question why a year is assumed to have 365.25 days, while a month has 30 days, here is what the source has to say in src/include/datatype/timestamp.h:

/*
 * Assorted constants for datetime-related calculations
 */

#define DAYS_PER_YEAR   365.25  /* assumes leap year every four years */
#define MONTHS_PER_YEAR 12
/*
 *      DAYS_PER_MONTH is very imprecise.  The more accurate value is
 *      365.2425/12 = 30.436875, or '30 days 10:29:06'.  Right now we only
 *      return an integral number of days, but someday perhaps we should
 *      also return a 'time' value to be used as well.  ISO 8601 suggests
 *      30 days.
 */
#define DAYS_PER_MONTH  30              /* assumes exactly 30 days per month */
#define HOURS_PER_DAY   24              /* assume no daylight savings time changes */

/*
 *      This doesn't adjust for uneven daylight savings time intervals or leap
 *      seconds, and it crudely estimates leap years.  A more accurate value
 *      for days per years is 365.2422.
 */
#define SECS_PER_YEAR   (36525 * 864)   /* avoid floating-point computation */
#define SECS_PER_DAY    86400
#define SECS_PER_HOUR   3600
#define SECS_PER_MINUTE 60
#define MINS_PER_HOUR   60

So I guess the reason is that ISO 8601 decrees that a month has 30 days.

How do I convert an interval into a number of hours with postgres?

Probably the easiest way is:

SELECT EXTRACT(epoch FROM my_interval)/3600

Convert interval to number in postgresql

You can get the number of seconds in an interval like this:

SELECT EXTRACT(epoch FROM INTERVAL '1 day 30 minutes 1.234 seconds');

┌───────────┐
│ date_part │
├───────────┤
│ 88201.234 │
└───────────┘
(1 row)

How to find difference b/w TIMESTAMP format values in Oracle?

The result of timestamp arithmetic is an INTERVAL datatype. You have an INTERVAL DAY TO SECOND there...

If you want the number of minutes one way would be to use EXTRACT(), for instance:

select extract( minute from interval_difference )
        + extract( hour from interval_difference ) * 60
        + extract( day from interval_difference ) * 60 * 24
  from ( select systimestamp - (systimestamp - 1) as interval_difference
           from dual )

Alternatively you can use a trick with dates:

select sysdate + (interval_difference * 1440) - sysdate
  from (select systimestamp - (systimestamp - 1) as interval_difference
          from dual )

The "trick" version works because of the operator order of precedence and the differences between date and timestamp arithmetic.

Initially the operation looks like this:

date + ( interval * number ) - date

As mentioned in the documentation:

Oracle evaluates expressions inside parentheses before evaluating those outside.

So, the first operation performed it to multiply the interval by 1,440. An interval, i.e. a discrete period of time, multiplied by a number is another discrete period of time, see the documentation on datetime and interval arithmetic. So, the result of this operation is an interval, leaving us with:

date + interval - date

The plus operator takes precedence over the minus here. The reason for this could be that an interval minus a date is an invalid operation, but the documentation also implies that this is the case (doesn't come out and say it). So, the first operation performed is date + interval. A date plus an interval is a date. Leaving just

date - date

As per the documentation, this results in an integer representing the number of days. However, you multiplied the original interval by 1,440, so this now represented 1,440 times the amount of days it otherwise would have. You're then left with the number of seconds.

It's worth noting that:

When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error. For example, the next two statements return errors:

The "trick" method will fail, rarely but it will still fail. As ever it's best to do it properly.

extracting HOUR from an interval in spark sql

I think you have to do the maths with this one as datediff in SparkSQL only supports days. This worked for me:

SELECT (unix_timestamp(to_timestamp('2021-01-22T05:00:00') ) - unix_timestamp(to_timestamp('2021-01-01T09:00:00'))) / 60 / 60 diffInHours

My results (in Synapse Notebook, not Databricks but I expect it to be the same):

The unix_timestamp function converts the timestamp to a Unix timestamp (in seconds) and then you can apply date math to it. Subtracting them gives the number of seconds between the two timestamps. Divide by 60 for the number minutes between the two dates and by 60 again for the number of hours between the two dates.

Extract date from datetime and calculate total minutes from duration

Based in varchar input, this query produces your desired result, exactly:

SELECT *
     , to_char(start_time::timestamp, 'FMMM/DD/YYYY') AS start
     , to_char(end_time::timestamp, 'FMMM/DD/YYYY') AS end
     , extract(epoch FROM duration::interval)::int / 60 AS duration_minutes
FROM   tbl;

Major points:

• Use timestamp and interval instead of varchar to begin with.
  
  Or do not store the functionally dependent column duration at all. It can cheaply be computed on the fly.

• For display / a particular text representation use to_char().
  
  Be explicit and do not rely on locale settings that may change from session to session.
  
  The FM pattern modifier is for (quoting the manual):

  fill mode (suppress leading zeroes and padding blanks)

• extract (epoch FROM interval_tpe) produces the number of contained seconds. You want to truncate fractional minutes? Integer division does just that, so cast to int like demonstrated. Related:

  • Get difference in minutes between times with timezone

---

Related Topics