Increment Oracle time in varchar field by a certain amount?
We have times stored in an Oracle varchar(5) field.
Times stored using HH24:MI (ie: 22:30, 10:15).
How can we run a query that will increase or decrease these times by a certain amount?开发者_StackOverflow社区 Ie: increase by one hour or decrease by 45 minutes.
you could use the built-in date (and interval -- thanks Alex for the link) calculation:
to_char(to_date(:x, 'hh24:mi') + INTERVAL :y MINUTE,'hh24:mi')
for instance:
SQL> WITH my_data AS (
2 SELECT '12:15' t FROM dual
3 UNION ALL SELECT '10:30' FROM dual
4 )
5 SELECT t,
6 to_char(to_date(t, 'hh24:mi') + INTERVAL '15' MINUTE,'hh24:mi')"t+15"
7 FROM my_data;
T t+15
----- -----
12:15 12:30
10:30 10:45
The functions to convert from/to date and char are TO_DATE()
and TO_CHAR()
:
SELECT
TO_DATE('31/12/2001 23:55:00', 'DD/MM/YYYY HH24:MI:SS') AS "DATE",
TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YYYY HH24:MI:SS') AS "CHAR"
FROM DUAL
So you can do:
SELECT
TO_DATE('23:45', 'HH24:MI'),
TO_DATE('23:45', 'HH24:MI') + INTERVAL '45' MINUTE,
TO_CHAR(TO_DATE('23:45', 'HH24:MI') + interval '45' MINUTE, 'HH24:MI')
FROM DUAL
There're possibly other better ways to do it (I'm not sure this will work as expected if ran when DST is about to start) but I'm still learning :)
精彩评论