About Oracle Date and useful functions

Oracle has powerful date functions. Sometimes you need to convert it to another type or another type has to be converted to date type or you need trunc date to display only part of date or anything else.

So, you can lots of things on date type. I will try to tell some useful operations on date types.

Date type placed as 7 bytes on disk. It stores year, month, day, hour, minute, second. Timestamp type placed as 11 bytes. And it stores addition to date fraction of seconds and time zone information. Especially, it is useful for real time applications to measure where does the time pass. ( because of I used this type )

For example for 15.05.2009 18:01:00, Oracle do not store it as how it was written. It is stored as a different internal style as 7 bytes and this style of storing is advantageous when you trunc or convert it to another type or sort date datas.

Formatting date is useful and necessary. When you rely on the default date format, your application may be in the future affected negatively ( I have experienced it twice ).

First and most known function is to_date and to_char functions.
For DATE type, you can convert this date type to char :

TO_CHAR(date,’DATEFORMAT’);
If you do not specify the date format, your date type will be trunced.

SQL> select to_Char(sysdate) from dual;

TO_CHAR(SYSDATE)
—————-
10/06/2009

What is the date format? What part of dates can be specified?
There are lots of documents on web that mentions about it and i don’t repeat it
( Oracle Date Parts that can be extracted ). Most known and used of them are DD, MM, YYYY, HH24, HH, MI, SS.

For example :

SQL> select to_Char(sysdate,’DD/MM/YYYY HH24:MI:SS’) from dual;

TO_CHAR(SYSDATE,’DD/MM/YYYYHH2
——————————
10/06/2009 23:39:39

So far, we saw that how date type can be converted to char. Inversely, convert char to date is similar to it.

SQL> select to_date(’12:00:00 04.06.2009′,’HH24:MI:SS DD.MM.YYYY’) from dual;

TO_DATE(’12:00:0004.06.2009′,’
——————————
6/4/2009 12:00:00 PM

Date Arithmetics and Useful other Date functions:

Adding X days to a date you use “datevariable + 1″ : SYSDATE + 1
Adding X seconds to a date you use “datevariable + 1/(24*60*60)” : SYSDATE + 1/(24*60*60)
Another adding or substracting operations you can use your mind

Adding months you can use ADD_MONTHS(variable,#months):

SQL> select sysdate, add_months(sysdate,1), add_months(add_months(sysdate,1),1) from dual;

SYSDATE ADD_MONTHS(SYSDATE,1) ADD_MONTHS(ADD_MONTHS(SYSDATE,
———– ——————— ——————————
6/10/2009 1 7/10/2009 11:55:22 PM 8/10/2009 11:55:22 PM

Format is : MONTH DAY YEAR HH24 MI SS

No difference, if current month has 30 or 31 days only month number will increase 1. If we are in the 30 January, according to year add_months(date,1) will return 28-Feb or 29-Feb.

Months_Between :

SQL> select months_between(sysdate+75,sysdate) from dual;

MONTHS_BETWEEN(SYSDATE+75,SYSD
——————————
2.45161290322581

You can trunc it to 2.

First Day Of Month :

SQL> SELECT TO_CHAR(TRUNC(SYSDATE,’MONTH’),’DAY’) FROM DUAL;

TO_CHAR(TRUNC(SYSDATE,’MONTH’)
——————————
MONDAY

Last Day Of Month :

SQL> SELECT TO_CHAR(LAST_DAY(SYSDATE),’DAY’) FROM DUAL;

TO_CHAR(LAST_DAY(SYSDATE),’DAY
——————————
TUESDAY

When next first Friday :

SQL> SELECT TO_CHAR(NEXT_DAY(SYSDATE,’FRIDAY’),’DD’) FROM DUAL;

TO_CHAR(NEXT_DAY(SYSDATE,’FRID
——————————
12

First day of Month

SQL> SELECT TO_CHAR(LAST_DAY(add_months(SYSDATE,-1))+1,’DAY’) FROM DUAL;

TO_CHAR(LAST_DAY(ADD_MONTHS(SY
——————————
PAZARTESI

Now, which # of week we are in :

SQL> select to_Char(sysdate,’IW’) from dual;

TO_CHAR(SYSDATE,’IW’)
———————
24
SQL>

EXTRACT
To extract specified date part from date or timestamp variable. Alternative way is convert date to char and perform substr operation. Former takes less time than latter.

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )

SQL> select extract(hour from systimestamp)||’:'||extract(minute from systimestamp)||’:'||extract(second from systimestamp) from dual;

EXTRACT(HOURFROMSYSTIMESTAMP)|
——————————————————————————–
7:40:7,142039

HOUR, SECOND, MINUTE, TIMEZONE_HOUR, TIMEZONE_MINUTE can be extracted only timestamp.

A number that how many days, hours, minutes, seconds : NUMTODSINTERVAL(num,):

SQL> select numtodsinterval(1440,’MINUTE’) FROM DUAL;

NUMTODSINTERVAL(1440,’MINUTE’)
——————————
+000000001 00:00:00

1440 minutes equals to 1 day ( leftmost piece ).

SQL> SELECT NUMTODSINTERVAL(3602,’SECOND’) FROM DUAL;

NUMTODSINTERVAL(3602,’SECOND’)
——————————
+000000000 01:00:02

3602 seconds equals to 1 hour 2 seconds.

About NLS_DATE

To set default date format for session based.

SQL> ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY MM DD’;

Session altered

SQL> select sysdate from dual;

SYSDATE
———–
6/17/2009 1

SQL>

Converting unix timestamp to Oracle in PL/SQL
This is may useful your application to convert unix timestamp to oracle date.
Read this post
( Responsibility of adding or subtracting GMT is belong to you )

Another DATE functions :

# NUMTOYMINTERVAL, converts a NUMBER or expression into a YEAR TO MONTH interval.
# TO_DSINTERVAL converts string to DAY TO SECOND interval.
# TO_YMINTERVAL converts a character string to YEAR TO MONTH type

jSch — SSH Api for Java Applications — Ssh Port Forwarding in Java

If you write a java network application, for some security reasons you need to make an ssh connection to remote host. To make an ssh connection you need some parameters. Host address, user name and password are some important of them. To pass typing password you can use “send / expect” application or you can use jSch api that i am going to tell you.

SshHandler class main class of this api, it is like interface that provides method to pass user name and password. And Awaker class is my application class that reads file that includes connection information for each line.

Example line is:

nxxast01;turkcell;xyz;110.15.122.20;9911;127.0.0.1;9999

First piece is name of machine, second is username, third is password, fourth is remote host address, fifth is local port, sixth is where remote host will be directed and seventh is which port that remote host forward. I mentioned this to understand code ( by the way i am not a kind of java expert )

It means that machine 110.15.122.20 connects to 127.0.0.1:9999 then forward data to localhost:9911
( SSH Port Forwarding )

SshHandler Class :

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package nor;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.UserInfo;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
*
* @author TTASUNGUR
*/
public class SshHandler {
JSch jsch=new JSch();

public void openSshConnection(String ps_user,String ps_password, String ps_host, String ps_localport, String ps_remotehost, String ps_remoteport ) throws SshConnectionException
{
try
{
System.out.println(Global.info_prefix+”It will be trying to connect remote host : Username : “+ps_user+”, Password : , Remote Host : “+ps_host+”, Localport : “+ps_localport+”, Remote : “+ps_remotehost+”, RemotePort : “+ps_remoteport);
Session session= jsch.getSession(ps_user, ps_host, Global.sshport);
UserInfo ui = new MyUserInfo(ps_password);
session.setUserInfo(ui);
session.connect();
int assigned_port=session.setPortForwardingL(Integer.parseInt(ps_localport), ps_remotehost, Integer.parseInt(ps_remoteport));
System.out.println(Global.info_prefix+”Ssh connection is established for :” + ps_host +”. Localport:”+assigned_port+”:”+ps_remotehost+”:”+ps_remoteport);
}
catch (JSchException ex)
{
System.err.println(Global.error_prefix+”Ssh connection could not be established for :”+ps_host+”, due to :”+ex.getMessage());
ex.printStackTrace();
throw new SshConnectionException(Global.error_prefix+”Ssh connection could not be established for :”+ps_host+”, due to :”+ex.getMessage());
}
}

public static class MyUserInfo implements UserInfo
{
public String getPassword(){ return passwd; }

public MyUserInfo(String password)
{
this.passwd = password;
}

public boolean promptYesNo(String str){
System.out.println(str+”promptYesNo”);

return true;
}

String passwd;

public String getPassphrase(){ return null; }
public boolean promptPassphrase(String message){ return true; }
public boolean promptPassword(String message){
//System.out.println(“promptPassword”);
// passwd = message;
//passwd = “turkcell”;

return true;

}
public void showMessage(String message){
System.out.println(message);
}

public String[] promptKeyboardInteractive(String destination,
String name,
String instruction,
String[] prompt,
boolean[] echo){
return new String[3];
}
}
}

Awaker Class :
( important line is start with ssh.openSshConnection… )

package nor;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.UserInfo;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.Vector;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
*
* @author TTASUNGUR
*/
public class Awaker {

ExecutorService threadExecutor;
String s_location_file_name = “rtdf_location_ips.rtdf”;
SshHandler ssh = new SshHandler();
String sentinal_files_path;
public Awaker(String sentinal_files_path)
{
this.sentinal_files_path = sentinal_files_path;
}

public void setStreamLocations(String pstr_location_file_name)
{
FileReader l_fr = null;
String ls_temp=”",ls_temp_arr[],ls_local_port;

try
{
File l_f1 = new File(pstr_location_file_name);
l_fr = new FileReader(l_f1);
BufferedReader br = new BufferedReader(l_fr);

try
{
// first line was metada it can be passed
ls_temp = br.readLine();
}
catch (IOException ex)
{
ex.printStackTrace();
}

while ( ls_temp != null)
{
try
{
ls_temp = br.readLine();
System.out.println(Global.info_prefix+”Line was read : “+ls_temp);
ls_temp_arr = ls_temp.split(Global.location_file_splitter);

if ( ls_temp_arr.length != Global.numberof_location_file_fields)
{
System.out.println(Global.error_prefix+”This line has not “+Global.numberof_location_file_fields+” fields”);
}
else
{
try
{
ssh.openSshConnection(ls_temp_arr[1], ls_temp_arr[2], ls_temp_arr[3], ls_temp_arr[4], ls_temp_arr[5], ls_temp_arr[6]);
//StreamHandler sh1 = new StreamHandler(ls_temp_arr[0]+”(“+ls_temp_arr[3]+”)Local Port:”+ls_temp_arr[4],Global.localhost,Integer.parseInt(ls_temp_arr[4]));
//threadExecutor.submit(sh1);

}
catch (SshConnectionException ex)
{
System.out.println(ex.getMessage());
}

}

}
catch (IOException ex)
{
System.out.println(Global.error_prefix+”The line could not be read!”);

}

}
}
catch (FileNotFoundException ex)
{
System.out.println(Global.error_prefix+”Location file are not found : “+s_location_file_name);
ex.printStackTrace();
}
finally
{
try
{
l_fr.close();
}
catch (IOException ex)
{
ex.printStackTrace();
}
}

}

public void startThreads()
{

threadExecutor = Executors.newCachedThreadPool();

FileHandler fh;
fh = new FileHandler(sentinal_files_path);
fh.run();

// stream locations will be set
setStreamLocations(s_location_file_name);

threadExecutor.shutdown(); // nonsense code
if ( threadExecutor.isTerminated() && threadExecutor.isShutdown() )
{
// this message should not be displayed any time.
// because application will not never finish.
System.out.println(“Info : Application has been finished.”);
}
}

}

To download jSch Api, click here.