Format Interval with To_Char

format interval with to_char

I realize it's not clever at all, nor is it the special format string you're looking for, but this answer does work, given that the output is fixed length:

SELECT    SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 1, 1)
       || SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 9, 2)
       || ' '
       || SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 12, 12)
  FROM table1;

It also just truncs the fractional seconds instead of rounding, but I assume from your example they're all just zeros anyway.

This is an even greater embarrassment, but I couldn't resist:

SELECT SUBSTR(REPLACE(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00')))
                     , '0000000', '')
             , 1, 16)
  FROM table1;

Adding a day in Oracle but losing hour and minute, and format is also changing

You are converting your string into a date or timestamp, and adjusting it by a day. Your client then decides how to format that for display, usually using you session setting like NLS_DATE_FORMAT.

If you want to display (or store*) the value in a particular format then you should specify that, with to_char(), e.g.:

TO_CHAR(TO_DATE(col1,'MM/DD/YYYY HH24:MI') + INTERVAL '1' DAY,'MM/DD/YYYY HH24:MI')

09/28/0021 18:05

or if you want to suppress some leading zeros to match your original string you can toggle those with the FM modifier:

TO_CHAR(TO_DATE(col1,'MM/DD/YYYY HH24:MI') + INTERVAL '1' DAY,'FMMM/DD/YYYY HH24:FMMI')

9/28/21 18:05

As you can see in the output of first of those, and as @Aitor mentioned, the year comes out as 0021 rather than 21. That's because you used a four-digit YYYY mask for a 2-digit year value. In the second one the FM suppresses that, so it's less obvious. As you don't seem to care about the century it usually doesn't matter whether you use YY or RR - the exception maybe being if you happen to hit a leap year/day; but it's still better to have the mask match the string, so with RR:

TO_CHAR(TO_DATE(col1,'MM/DD/RR HH24:MI') + INTERVAL '1' DAY,'FMMM/DD/RR HH24:FMMI')

9/28/21 18:05

db<>fiddle

* But you should not be storing dates as strings. They should be stored as dates, and formatted as strings for display only. You shouldn't really be using 2-digit years any more either.

Why postgres show two different format for same interval value?

When an interval is a difference between two timestamps it is always justified to hours (i.e. it has standard format). Examples:

select
    '2015-01-01 13:0:0'::timestamp - '2014-01-01 23:0:0'::timestamp, --> 364 days 14:00:00
    '2015-01-01 13:0:0'::timestamp - '2014-01-01 03:0:0'::timestamp, --> 365 days 10:00:00
    '2015-01-01 13:0:0'::timestamp - '2015-01-01 03:0:0'::timestamp; --> 10:00:00

Calculations on intervals are executed on date part and time part separately, so they may lead to strange formats. Examples:

select 
    '2 day 1:00:00'::interval- '1 day 2:00:00'::interval,    --> 1 day -01:00:00 (!!)
    '2 day 100:00:00'::interval+ '1 day 60:00:00'::interval, --> 3 days 160:00:00
    '2 day 100:00:00'::interval- '2 day 60:00:00'::interval; --> 40:00:00

For such cases Postgres developers provided the appropriate function for the format standardization:

select 
    justify_hours('1 day -01:00:00'),  --> 23:00:00
    justify_hours('3 days 160:00:00'), --> 9 days 16:00:00
    justify_hours('40:00:00');         --> 1 day 16:00:00

However they did not think that the reverse operation would be needful. In this answer I proposed a function to convert a date part of an interval to hours. I think it can be (with some minor changes) some kind of reverse function for justify_hours():

create or replace function unjustify_hours(interval)
returns interval language sql as $$
    select format('%s:%s',
        (extract (epoch from $1) / 3600)::int,
        to_char($1, 'mi:ss'))::interval;
$$;

select 
    unjustify_hours('23:00:00'),        --> 23:00:00
    unjustify_hours('9 days 16:00:00'), --> 232:00:00
    unjustify_hours('1 day 16:00:00');  --> 40:00:00

The function to_char(interval, text) cannot be helpful here, as

select 
    to_char(interval '23:00:00', 'hh24:mi:ss'),        --> 23:00:00
    to_char(interval '9 days 16:00:00', 'hh24:mi:ss'), --> 16:00:00 (!)
    to_char(interval '1 day 16:00:00',  'hh24:mi:ss'); --> 16:00:00 (!)

Note that an interval can be correctly formatted in many ways:

select 
    justify_hours('100:00:00'),        --> 4 days 04:00:00
    justify_hours('1 days 76:00:00'),  --> 4 days 04:00:00
    justify_hours('2 days 52:00:00'),  --> 4 days 04:00:00
    justify_hours('5 days -20:00:00'); --> 4 days 04:00:00

Per the documentation:

According to the SQL standard all fields of an interval value must
have the same sign, so a leading negative sign applies to all fields;
for example the negative sign in the interval literal '-1 2:03:04'
applies to both the days and hour/minute/second parts. PostgreSQL
allows the fields to have different signs, and traditionally treats
each field in the textual representation as independently signed, so
that the hour/minute/second part is considered positive in this
example. If IntervalStyle is set to sql_standard then a leading sign
is considered to apply to all fields (but only if no additional signs
appear). Otherwise the traditional PostgreSQL interpretation is used.
To avoid ambiguity, it's recommended to attach an explicit sign to
each field if any field is negative.

and

Internally interval values are stored as months, days, and seconds.
This is done because the number of days in a month varies, and a day
can have 23 or 25 hours if a daylight savings time adjustment is
involved. The months and days fields are integers while the seconds
field can store fractions. Because intervals are usually created from
constant strings or timestamp subtraction, this storage method works
well in most cases. Functions justify_days and justify_hours are
available for adjusting days and hours that overflow their normal
ranges.

Properly format postgres interval to HH:MM

We can use TO_CHAR with HH24:MI format after subtracting from two datetime.

SELECT 
    employee_id,
    clock_in,
    clock_out,
    TO_CHAR(clock_out - clock_in,'HH24:MI')
FROM
    payroll_timelog
WHERE
    employee_id=31;

sqlfiddle

Converting time difference to a given format in Oracle

If dates are differ only in time part you can use interval day to second. For instance:

SQL> select (to_date('25.12.12 15:37:32', 'DD.MM.YY HH24:MI:SS')
  2        - to_date('25.12.12 12:45:45', 'DD.MM.YY HH24:MI:SS')) day(0) to second(0) as Time
  3    from dual
  4  ;

TIME
-------------
+0 02:51:47

But obviously it will not always be the case. So you could write a long query to calculate different parts of time, but I think I would go with this simple function:

SQL> create or replace function DaysToTime(p_val in number)
  2  return varchar2
  3  is
  4    l_hours    number;
  5    l_minutes  number;
  6    l_seconds  number;
  7  begin
  8    l_Hours := 24 * p_val;
  9    l_minutes := (l_hours - trunc(l_hours)) * 60;
 10    l_seconds := (l_minutes - trunc(l_minutes)) * 60;
 11    return to_char(trunc(l_hours), 'fm09')  ||':'||
 12           to_char(trunc(l_minutes), 'fm09')||':'||
 13           to_char(trunc(l_seconds), 'fm09');
 14  end;
 15  /

Function created

And now the query would be:

SQL> select DaysToTime(to_date('25.12.12 15:37:32', 'DD.MM.YY HH24:MI:SS')
  2        - to_date('25.12.12 12:45:45', 'DD.MM.YY HH24:MI:SS')) as Time
  3    from dual
  4  ;

TIME
----------
02:51:47

---

Related Topics