Extract Time Part from Timestamp Column in Oracle

Extract time part from TimeStamp column in ORACLE

What about EXTRACT() function?

How can I extract just the time component from an Oracle timestamp value?

You should be able to do

select to_number( to_char( request_time, 'HH24' ) ),
       count(*)
  from service_request
 group by to_number( to_char( request_time, 'HH24' ) );

The to_number is probably not strictly necessary but it makes more sense to return a numeric hour than a string hour for things like sorting.

If the data type is actually a timestamp, then you could improve this

select extract( hour from request_time ),
       count(*)
  from service_request
 group by extract( hour from request_time );

How to extract time from a date column in the where clause in ORACLE?

With some sample data you can see that using HH12 doens't necessarly produce the strings you are expecting:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

-- CTE just for dummy data
with mytable (mydate) as (
            select cast(timestamp '2018-08-01 00:00:00' as date) from dual
  union all select cast(timestamp '2018-08-02 07:59:59' as date) from dual
  union all select cast(timestamp '2018-08-03 08:00:00' as date) from dual
  union all select cast(timestamp '2018-08-04 08:00:01' as date) from dual
  union all select cast(timestamp '2018-08-05 19:59:59' as date) from dual
  union all select cast(timestamp '2018-08-06 20:00:00' as date) from dual
  union all select cast(timestamp '2018-08-07 20:00:01' as date) from dual
)
-- actual query
select mydate,
  to_char(mydate, 'HH24:MI:SS') as time_24,
  to_char(mydate, 'HH12:MI:SS') as time_12
from mytable;

MYDATE              TIME_24  TIME_12 
------------------- -------- --------
2018-08-01 00:00:00 00:00:00 12:00:00
2018-08-02 07:59:59 07:59:59 07:59:59
2018-08-03 08:00:00 08:00:00 08:00:00
2018-08-04 08:00:01 08:00:01 08:00:01
2018-08-05 19:59:59 19:59:59 07:59:59
2018-08-06 20:00:00 20:00:00 08:00:00
2018-08-07 20:00:01 20:00:01 08:00:01

So when you try to filter using that HH12-based string it includes records you don't expect to see, between 8am and 8pm; and also excludes midnight (as that is '12:00:00' not '00:00:00'):

select mydate
from mytable
where to_char(mydate, 'HH12:MI:SS') between '00:00:00' and '08:00:00';

MYDATE             
-------------------
2018-08-02 07:59:59
2018-08-03 08:00:00
2018-08-05 19:59:59
2018-08-06 20:00:00

If you use HH24 instead then you get

select mydate
from mytable
where to_char(mydate, 'HH24:MI:SS') between '00:00:00' and '08:00:00';

MYDATE             
-------------------
2018-08-01 00:00:00
2018-08-02 07:59:59
2018-08-03 08:00:00

Also, notice that between is inclusive, so it picks up records at exactly 08:00:00. That may not be what you want - if you're splitting the day into three 8-hour periods, you don't data for that second to be included multiple times; so you can use a more explicit range instead:

select mydate
from mytable
where to_char(mydate, 'HH24:MI:SS') >= '00:00:00'
and to_char(mydate, 'HH24:MI:SS') < '08:00:00';

MYDATE             
-------------------
2018-08-01 00:00:00
2018-08-02 07:59:59

then your second shift is:

where to_char(mydate, 'HH24:MI:SS') >= '08:00:00'
and to_char(mydate, 'HH24:MI:SS') < '16:00:00';

and your third shift is:

where to_char(mydate, 'HH24:MI:SS') >= '16:00:00';

or if you prefer, for consistency:

where to_char(mydate, 'HH24:MI:SS') >= '16:00:00'
and to_char(mydate, 'HH24:MI:SS') < '24:00:00';

You can't ever get the hour reported as 24 but as it's a string comparison that doesn't matter here, though it is slightly jarring.

SQL How do I extract time from a date to HH:MI:SS PM/AM?

You can use TO_CHAR( datetime, format ) to format a DATE column. The format options are given here.

This would give you the time part of a date value (12-hour clock plus AM/PM):

SELECT TO_CHAR( column_name,'HH:MI:SS AM') 
FROM   your_table

Edit - Addressing the update

You can do:

SELECT CASE WHEN TO_NUMBER( TO_CHAR( a.dtime_appl_creation, 'HH24' ) ) >= 12
            THEN TO_CHAR( a.dtime, 'HH:MI:SS PM' )
            END AS "date"
FROM   table_name

How to extract only Time from a DateTime field in Oracle SQL Developer?

Assuming your goal is to generate a string representing the time (which is what the query you posted returns despite the extraneous to_number calls)

SELECT to_char( <<column_name>>, 'HH24:MI:SS' )
  FROM table_name

If you want to return a different data type, you'd need to tell us what data type you want to return. If, for example, you really want to return an INTERVAL DAY TO SECOND

SELECT numtodsinterval( <<column name>> - trunc(<<column name>>), 'day' )
  FROM table_name

Oracle SQL: Extract Date & Time from Timestamp Timezone

I have a START_DATE column in my table with values in the format YYYY-MM-DD HH:MM:SS AM/PM GMT

Assuming that your START_DATE column is of the DATE data type then your statement is incorrect; a DATE column has no format and it is stored internally as 7-bytes. It is only when it is passed to the user interface you are using to access the database that that UI will format the date (and not the database). SQL/Plus and SQL developer will format the date using the NLS_DATE_FORMAT session parameter (which is set per user session and can be changed) so relying on this format can lead to "interesting" bugs where the code you are using does not change but will start and stop working for different users depending on their session settings.

You can just do:

INSERT INTO TABLE2 (
    DATE_WITH_TIMEZONE,
    DATE_WITHOUT_TIMEZONE
)
SELECT FROM_TZ( CAST( START_DATE AS TIMESTAMP ), 'GMT' ),
       START_DATE
FROM   TABLE_DATE;

If your START_DATE column is of a string datatype (why would you do this?) then you will need to convert it to the appropriate type:

INSERT INTO TABLE2 (
    DATE_WITH_TIMEZONE,
    DATE_WITHOUT_TIMEZONE
)
SELECT TO_TIMESTAMP_TZ( START_DATE, 'YYYY-MM-DD HH12:MI:SS AM TZR' ),
       CAST( TO_TIMESTAMP_TZ( START_DATE, 'YYYY-MM-DD HH12:MI:SS AM TZR' ) AS DATE )
FROM   TABLE_DATE;

Get date and time from date column in oracle

Date need not be converted into date again.

You can simply write your query like this:

SELECT
    TIME -- use to_char for formatting the output date
FROM Table
WHERE
    TIME <= TO_DATE('08/07/2019 10:13:52', 'MM/DD/YYYY HH24:MI:SS')

Cheers!!

Getting date in oracle from timestamp without trailing o's?

I have been scouring stack and google to find a way to extract a date from timestamp w/o the trailing zero's still being a date and NOT a VARCHAR variable

You cannot if you want the value to still be a DATE

A DATE is stored internally as 7-bytes and always has year (2 bytes), month, day, hour, minute and second (1 byte each) components.

A TIMESTAMP is stored internally with 11-20 bytes with the same year-to-second components as a DATE but also fractional seconds components and optional time zone components.

Neither a DATE nor a TIMESTAMP has a format because they are just binary data and you cannot remove the time component because they both always have a time component.

SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL

gives: 18-JUL-12 00:00:00

The query outputs a DATE and depending on the user interface that you use then the date may be implicitly cast to a string for outputting. SQL/Plus and SQL Developer use the NLS_DATE_FORMAT session parameter to implicitly convert DATEs to strings when it is displaying it to the user:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;

Outputs: 2012-07-18 13:27:18

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT TRUNC(to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;

Outputs: 2012-07-18

However, ANY user can change their own session parameters at ANY time so you should never rely on implicit formatting of dates.

If you want a DATE then you should not expect it to be formatted in any particular way as it is just binary data. If you want a formatted date then you should explicitly convert it to a string with the formatting you require using TO_CHAR:

SELECT TO_CHAR(
         TRUNC( to_date('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS') ),
         'DD/MM/YYYY'
       )
FROM   DUAL;

Outputs: 18/07/2012

db<>fiddle

---

Related Topics