CODEWITHSUNDEEP
mysqloracle-database
Yogesh
Yogesh

Reputation: 807

How to set fix time minute and second with current hour in Oracle

I want to fetch data between fix time interval.

example :(This is working in mysql and I want it in oracle)

Mysql

 $ReportStartDate = "DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 HOUR) ,'%Y-%m-%d %H:00:00')"
 $ReportEndDate   = "DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 HOUR),'%Y-%m-%d %H:59:59')";

This is working fine in mysql.How it is possible in oracle..??

Upvotes: 1

Views: 3384

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

You can do it without back and forth converting of DATE and string.

SELECT 
    TO_CHAR(TRUNC(SYSDATE, 'HH'), 'YYYY-MM-DD HH24:MI:SS'),
    TO_CHAR(TRUNC(SYSDATE, 'HH') + INTERVAL '59:59' MINUTE TO SECOND, 'YYYY-MM-DD HH24:MI:SS')
FROM dual;

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

$ReportStartDate = "DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 HOUR) ,'%Y-%m-%d %H:00:00')"

$ReportEndDate = "DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 HOUR),'%Y-%m-%d %H:59:59')";

You could convert it as:

$ReportStartDate = to_date(to_char(SYSDATE, 'dd-mon-yyyy hh24')||':00:00', 'dd-mon-yyyy hh24:mi:ss')
$ReportEndDate   = to_date(to_char(SYSDATE, 'dd-mon-yyyy hh24')||':59:59', 'dd-mon-yyyy hh24:mi:ss')

You could use TO_DATE with proper FORMAT MODEL. And use BETWEEN to filter the rows.

For example, I want to fetch all the employees hired between a time period:

SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> SELECT empno,
  2    hiredate
  3  FROM EMP
  4  WHERE HIREDATE BETWEEN
  5  TO_DATE('01-apr-1981 00:00:00','dd-mon-yyyy hh24:mi:ss')
  6  AND
  7  TO_DATE('01-dec-1981 23:59:59','dd-mon-yyyy hh24:mi:ss')
  8  /

     EMPNO HIREDATE
---------- --------------------
      7566 02-apr-1981 00:00:00
      7654 28-sep-1981 00:00:00
      7698 01-may-1981 00:00:00
      7782 09-jun-1981 00:00:00
      7839 17-nov-1981 00:00:00
      7844 08-sep-1981 00:00:00

6 rows selected.

SQL>

UPDATE OP wants the hour value should be from sysdate, however, minutes and seconds should be 00:00.

You could extract the hours, and append 00:00 as minutes and seconds value:

SQL> SELECT to_char(SYSDATE, 'dd-mon-yyyy hh24:mi:ss') currentdate,
  2  to_char(SYSDATE, 'dd-mon-yyyy hh24')||':00:00' dt
  3  FROM dual;

CURRENTDATE          DT
-------------------- --------------------
20-feb-2015 12:19:39 20-feb-2015 12:00:00

SQL>

To use the value in any DATETIME operations, convert it to DATE using TO_DATE:

to_date(to_char(SYSDATE, 'dd-mon-yyyy hh24')||':00:00', 'dd-mon-yyyy hh24:mi:ss')

Upvotes: 0

San
San

Reputation: 4538

It seems that you want the values in character datatype in 'YYYY-MM-DD HH24:MI:SS' format and the interval has to be of last hour. NOW() in Oracle is represented by SYSDATE function. DATE_SUB can be replace by direct subtraction of one hour. 

$ReportStartDate = "to_char(trunc(sysdate - 1/24, 'HH'), 'YYYY-MM-DD HH24:MI:SS')";
$ReportEndDate   = "to_char(trunc(sysdate - 1/24, 'HH') + (3599/3600)/24, 'YYYY-MM-DD HH24:MI:SS')";

Update: You can replace sysdate with any date you want.

Upvotes: 1

Related Questions