Can we extract time from date column that store only date not time?

Question

i have a table with the column date(datecreated) and the example data 2.10.2017 So i want to know time the data inserted but i only store date not time. It is possible to me to extract time?

Answer 1

The time component is certainly there, and you can see it by using TO_CHAR:

SELECT TO_CHAR(DATECREATED, 'DD-MON-YYYY HH24:MI:SS')
  FROM YOUR_TABLE;

However, if you didn't set the time when the record was created, the HOUR, MINUTE, and SECOND fields should all be zero. In other words if you did something like

INSERT INTO YOUR_TABLE (DATECREATED) VALUES (TO_DATE('09-OCT-2010', 'DD-MON-YYYY'));

the time components of the date will be zero.

dbfiddle here

Answer 2

The type date in Oracle also includes hours, minutes, etc. So unless you truncated that on insertion, it is stored. But in default setting, some clients don't show them.

Try

SELECT to_char(datecreated, 'YYYY-MM-DD HH24:MI:SS')
       FROM elbat;

to get the the year, month, day, hour, minute and second.

Answer 3

This is a common misconception - A DATE data type is always stored in Oracle tables as 7 bytes containing year (2 bytes), month, day, hours, minutes and seconds (1 byte each). It will never only store the year/month/day component.

However, if you do not give Oracle any information on what to put into the time component then Oracle will set those components to their default values of zero.

So if you do:

CREATE TABLE table_name ( value DATE );
INSERT INTO table_name
  SELECT DATE '2018-01-01' FROM DUAL UNION ALL
  SELECT CAST( TIMESTAMP '2018-10-04 08:20:14' AS DATE ) FROM DUAL;

Then:

SELECT TO_CHAR( value, 'YYYY-MM-DD"T"HH24:MI:SS' ) AS formatted,
       DUMP( value ) AS dmp,
       EXTRACT( HOUR FROM CAST( value AS TIMESTAMP ) ) As hr
FROM   table_name;

Outputs:

FORMATTED           DMP                                HR
------------------- ---------------------------------- --
2018-01-01T00:00:00 Typ=12 Len=7: 120,118,1,1,1,1,1     0
2018-10-04T08:20:14 Typ=12 Len=7: 120,118,10,4,9,21,15  8

Showing that the time components are there; you can extract them using TO_CHAR or EXTRACT functions; and if you don't specify values they default to zero.

i only store date not time. It is possible to me to extract time

No, if you have not specified a time then Oracle will default the hours, minutes and seconds of the date value to 00:00:00.

If you used SYSDATE (or something similar) to insert the dates then it will have a non-zero time component and the time component is just not being displayed by your user interface you can use TO_CHAR, etc. to override your user interface's default date format and get the information.