How to add minutes to a DATE type using only to_char and to

As mentioned in title, I am looking for way to add minutes onto a DATE type attribute without converting it to another data type.

( SELECT to_char(event_starttime, 'HH:MI:SS')   to_char('00:06:44', 'HH:MI:SS')
FROM
    event
WHERE
    event_id = (
        SELECT
            event_id
        FROM
            event
        WHERE
                carn_date = to_date((
                    SELECT
                        carn_date
                    FROM
                        carnival
                    WHERE
                        carn_name = 'RM Autumn Series Caulfield 2022'
                ), 'DD/MM/YYYY')
            AND eventtype_code = '21K'
    )
);

But it does not seem to be working. Converting to other data type is not possible and I can only use to_char and to_date to do it.

Once again, thank you for your help

CodePudding user response：

To add, for example, 15 minutes to a date just do this:

select event_starttime   (1/1440*15) from event;

CodePudding user response：

Converting to other data type is not possible
I can only use to_char and to_date to do it

Those two statements are contradictory. to_date() converts from a string data type to to a date data type; to_char() does the reverse.

But lets assume the second one is your actual requirement, and the assignment is not to use other functions like to_dsinterval() or other data types like intervals - however arbitrary a restriction that is.

If the starting date has its time set to midnight then you can convert just the date portion to a string, append the new time also still as a string, and convert the result to a date:

to_date(to_char(event_starttime, 'YYYY-MM-DD ') || '00:06:44', 'YYYY-MM-DD HH24:MI:SS')

Whether the starting date has its time as midnight or not, you can convert your time to a fraction of a day and add that; but that involves sysdate and trunc() as well as the two functions you mentioned:

event_starttime   (to_date('00:06:44', 'HH24:MI:SS') - trunc(sysdate, 'MM'))

That works because if you omit the date elements, to_date() gives you the specified time on the first day of the current month. And trunc(sysdate, 'MM') gives you midnight on that same day. Subtracting them gives you the fraction of a day that your time represents, which can then just be added to the original date, whatever time it already has.

db<>fiddle showing both, with the intermediate values so you can see what's happening.

CodePudding user response：

Do not use TO_DATE and TO_CHAR as that is converting it to another data-type (TO_CHAR converts the date to a string and TO_DATE converts a string to a date). Instead, add an INTERVAL to the DATE which will result in another DATE value (and an unchanged data-type):

SELECT event_starttime   INTERVAL '00:06:44' HOUR TO SECOND
FROM   event
WHERE  event_id = ( SELECT event_id
                    FROM   event
                    WHERE  carn_date =  SELECT TRUNC(carn_date)
                                        FROM   carnival
                                        WHERE  carn_name = 'RM Autumn Series Caulfield 2022'
                                      )
                    AND eventtype_code = '21K'
                  )

If you do not see a difference then change the preferences for how SQL Developer displays dates using:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';