Category Archives: Date

Java: Duration and Period Calculation


Almost every developer handle Duration and Period issues while need to do any calculation based on Date and Time. In Java 8 introduces new classes to handle Duration and period.

  • Period : A date period is measured in days, months, and years. Period can be calculated by java.time.Period class.
  • Duration: A time duration is measured in hours, minutes, and seconds. Duration can be calculated by java.time.Duration class.

Period period= Period.between(localDate1, localDate2);

Duration duration=Duration.between(localTime1, localTime2);

Duration.toString() method return duration as P1Y10M6D. Here duration is 1 year, 10 month and 6 days.

Period.toString() method return period as PT3H10M40S. Here duration is 3 hour, 10 minute and 40 seconds.

Example

Below is example to calculate Period and Duration  between two dates and times.

package com.date;

import java.time.Duration;
import java.time.LocalDate;
import java.time.LocalTime;
import java.time.Month;
import java.time.Period;
import java.time.format.DateTimeFormatter;
import java.time.temporal.ChronoUnit;

public class Java8DurationAndPeriodExample {

public static void main(String[] args) {

LocalDate june9th2016 = LocalDate.of(2016, Month.JUNE, 9);
LocalDate April15th2017 = LocalDate.of(2017, Month.APRIL, 15);
DateTimeFormatter format=DateTimeFormatter.ofPattern("MM/dd/yy");
// Compute period:
Period period=Period.between(April15th2017, june9th2016);
System.out.format("Period between %s and %s is %d year, %d month, %d days n" , April15th2017.format(format), june9th2016.format(format), period.getYears(), period.getMonths(), period.getDays());

// Add one month to August 10, 2018:
LocalDate April15th2017plusOneMonth=April15th2017.plus(1, ChronoUnit.MONTHS);
System.out.format("Adding one month to %s yields %s n", April15th2017.format(format), April15th2017plusOneMonth.format(format));

// Subtract one month from August 10, 2018:
LocalDate April15th2017minusOneMonth=April15th2017.plus(-1, ChronoUnit.MONTHS);
System.out.format("Subtracting one month from %s yields %s n", April15th2017.format(format), April15th2017minusOneMonth.format(format));

// Create two times for duration processing:
LocalTime twelveFifteenPM = LocalTime.of(12, 15, 9);
LocalTime twoTwentyFiveAM = LocalTime.of(2, 25, 0);
DateTimeFormatter timeFormat=DateTimeFormatter.ofPattern("HH:mm:ss a");
Duration duration=Duration.between(twoTwentyFiveAM, twelveFifteenPM);

System.out.format("Duration between %s and %s is %s n", twoTwentyFiveAM.format(timeFormat), twelveFifteenPM.format(timeFormat), duration);
duration=Duration.between(twelveFifteenPM, twoTwentyFiveAM);
System.out.format("Duration between %s and %s is %s n", twelveFifteenPM.format(timeFormat), twoTwentyFiveAM.format(timeFormat), duration);
	<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span>}
}

Output


Period between 04/15/17 and 06/09/16 is 0 year, -10 month, -6 days 
Adding one month to 04/15/17 yields 05/15/17 
Subtracting one month from 04/15/17 yields 03/15/17 
Duration between 02:25:00 AM and 12:15:09 PM is PT9H50M9S 
Duration between 12:15:09 PM and 02:25:00 AM is PT-9H-50M-9S 
Advertisements

[Solved] java.time.temporal. UnsupportedTemporalType Exception: Unsupported field: YearOfEra


UnsupportedTemporalTypeException is sub class of java.time.DateTimeException which is runtime checked exception. UnsupportedTemporalTypeException indicates that a ChronoField or ChronoUnit is not supported for a Temporal class.

Constructors

  • UnsupportedTemporalTypeException(String message): Constructs a new UnsupportedTemporalTypeException with the specified message.
  • UnsupportedTemporalTypeException(String message, Throwable cause): Constructs a new UnsupportedTemporalTypeException with the specified message and cause.

 

Exception Example

Below is example to convert Legacy date(java.util.Date) to java.time.Instant and vice versa.

package java.time.temporal;

import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.format.DateTimeFormatter;
import java.util.Date;

public class UnsupportedTemporalTypeExceptionExample {

public static void main(String[] args) {
SimpleDateFormat utilDateTimeFormat=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
//Exception on below line to resolve this issue uncomment at end of line.
DateTimeFormatter java8DateTimeFormat=DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");//.withZone(ZoneId.systemDefault());;
//Date to Instant
Date date=new Date();
Instant inst = date.toInstant();
System.out.println("Initial Date :"+utilDateTimeFormat.format(date));
System.out.println("Converted Instant :"+java8DateTimeFormat.format(inst));
//Instant To Date
Date newDate = Date.from(inst);
System.out.println("Converted Date :"+utilDateTimeFormat.format(newDate));
	}
}

Exception StackTrace


Initial Date :2018/08/04 08:11:53
Exception in thread "main" java.time.temporal.UnsupportedTemporalTypeException: Unsupported field: YearOfEra
    at java.time.Instant.getLong(Unknown Source)
    at java.time.format.DateTimePrintContext.getValue(Unknown Source)
    at java.time.format.DateTimeFormatterBuilder$NumberPrinterParser.format(Unknown Source)
    at java.time.format.DateTimeFormatterBuilder$CompositePrinterParser.format(Unknown Source)
    at java.time.format.DateTimeFormatter.formatTo(Unknown Source)
    at java.time.format.DateTimeFormatter.format(Unknown Source)
    at com.date.Legacy2Java8DateTimeInteroprability.main(Legacy2Java8DateTimeInteroprability.java:26)

Issue

The Instant class doesn’t contain Zone information, it only stores timestamp in milliseconds from UNIX epoch, i.e. 1 Jan 1070 from UTC. So, formatter can’t print a date because date always printed for concrete time zone. To format an Instant a time-zone is required. Without a time-zone, the formatter does not know how to convert the instant to human date-time fields, and therefore throws an exception.

Solutions

The time-zone can be added directly to the formatter using withZone().You should set time zone to formatter and all will be fine.

in above example you can uncomment commented section as below to solve this issue.


DateTimeFormatter java8DateTimeFormat=DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss").withZone(ZoneId.systemDefault());;

some more ways to format date


DateTimeFormatter formatter =
    DateTimeFormatter.ofLocalizedDateTime( FormatStyle.SHORT )
                     .withLocale( Locale.UK )
                     .withZone( ZoneId.systemDefault() );

or

DateTimeFormatter formatter = DateTimeFormatter.ofLocalizedDateTime(FormatStyle.SHORT).withLocale(Locale.UK).withZone(ZoneOffset.UTC);

Java: Legacy to Java 8 Date Time Interoperability


In Java 8 added some new methods with Legacy java.util Date and Time classes to convert object of java.time classes to get advantage new java 8 Date and Time classes with minimal changes.

Points To Remember:

  • java.util.Date and java.time.Instance classes are similar. The Date.from(Instant) and Date.toInstant() methods allow conversion between these classes. It represents instantaneous point of time in UTC which is independent of TimeZone  as epoch-seconds (since 1970-01-01T00:00:00Z) plus nanoseconds.
  • The java.time.ZonedDateTime class is the replacement for java.util.GregorianCalendar. The GregorianCalendar.from(ZonedDateTime) and GregorianCalendar.to(ZonedDateTime) methods faciliate conversions between these classes. It present time as follows:
    • LocalDate : year, month and day
    • LocalTime: hour, minute, second, nanoseconds
    • ZoneId: time zone
    • ZoneOffset: current offset from GMT
  • The ZoneOffset class specifies only an offset from Greenwich/UTC. The ZoneId class specifies a time zone identifier and has access to the rules used each time zone.
  • GeregorianCalendar with date as 1970-01-01 will use date component can be replaced with an instance of LocalDate.
  • GeregorianCalendar with time as 00:00 will use time component can be replaced with an instance of LocalTime.

java.util.Date to an java.time.Instant


Date date=new Date();
Instant instant=date.toInstant();

An java.time.Instant to java.util.Date


Date date=Date.from(instant);

java.util.Calendar to an java.time.Instant


Calendar cal=Calendar.getInstance();
Instant instant=cal.toInstant();

java.util.GregorianCalendar instance to an java.time.ZoneDateTime


Calendar gCal=GregorianCalendar.getInstance();
ZoneDateTime zoneDateTime=ZoneDateTime.ofInstant(gCal.toInstant(),ZoneId.systemDefault());

java.time.ZoneDateTime instance to java.util.GregorianCalendar


GregorianCalendar gCal=GregorianCalendar.from(zoneDateTime);

java.util.TimeZone object to java.time.ZoneId


Calendar gCal1=GregorianCalendar.getInstance();
TimeZone tz = cal.getTimeZone();
ZoneId zoneID=tz.toZoneId();
int tzoffset = cal.get(Calendar.ZONE_OFFSET);

Example

In below example considered all the cases of Legacy to Java 8 object classes.

package com.date;

import java.text.SimpleDateFormat;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.TimeZone;

public class Legacy2Java8DateTimeInteroprability {

public static void main(String[] args) {
SimpleDateFormat utilDateTimeFormat=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
DateTimeFormatter java8DateTimeFormat=DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss").withZone(ZoneId.systemDefault());;

//Date to Instant
Date date=new Date();
Instant inst = date.toInstant();
System.out.println("Initial Date :"+utilDateTimeFormat.format(date));
System.out.println("Converted Instant :"+java8DateTimeFormat.format(inst));

//Instant To Date
Date newDate = Date.from(inst);
System.out.println("Converted Date :"+utilDateTimeFormat.format(date));

//Calendar to ZoneDateTime
Calendar cal = Calendar.getInstance();
ZonedDateTime zdt = ZonedDateTime.ofInstant(cal.toInstant(), ZoneId.systemDefault());
System.out.println("Calendar to ZoneDateTime :"+java8DateTimeFormat.format(zdt));

//Calendar to Instant
Instant inst1 = cal.toInstant();
System.out.println("Calendar to Instant :"+java8DateTimeFormat.format(inst1));

//Geregorian calendar from Zone Date Time
GregorianCalendar gcal = GregorianCalendar.from(zdt);
System.out.println("Gregorian Calendar Date Time:"+utilDateTimeFormat.format(gcal.getTime()));
ZonedDateTime zdt1 = gcal.toZonedDateTime();
System.out.println("Calendar to ZoneDateTime Date :"+java8DateTimeFormat.format(zdt1));

LocalDateTime ldt = zdt1.toLocalDateTime();
System.out.println("Local Date Time :"+java8DateTimeFormat.format(ldt));
LocalDate localDate = zdt1.toLocalDate();
System.out.println("Local Date :"+java8DateTimeFormat.format(ldt));
LocalTime localTime = zdt1.toLocalTime();
System.out.println("Local Time :"+java8DateTimeFormat.format(ldt));

//TimeZone to Zone Id
Calendar gCal1=GregorianCalendar.getInstance();
TimeZone tz = cal.getTimeZone();
System.out.println("Time Zone :"+tz.getDisplayName());
ZoneId zoneID=tz.toZoneId();
System.out.println("Zone ID:"+zoneID.getId());
int tzoffset = cal.get(Calendar.ZONE_OFFSET);
<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span>System.out.println("Zone Offset:"+tzoffset);
	}
}

Output


Initial Date :2018/08/04 07:48:45
Converted Instant :2018/08/04 07:48:45
Converted Date :2018/08/04 07:48:45
Calendar to ZoneDateTime :2018/08/04 07:48:45
Calendar to Instant :2018/08/04 07:48:45
Gregorian Calendar Date Time:2018/08/04 07:48:45
Calendar to ZoneDateTime Date :2018/08/04 07:48:45
Local Date Time :2018/08/04 07:48:45
Local Date :2018/08/04 07:48:45
Local Time :2018/08/04 07:48:45
Time Zone :Pacific Standard Time
Zone ID:America/Los_Angeles
Zone Offset:-28800000

Java : How to get current date time?


In below example you will learn ways to get current date and time from Legacy Java Date and Calendar APIs and also from Java 8 LocalDateTime, LocalDate and LocalTime..

java.util.Date

Date date=new Date();

java.util.Calendar and java.util.GregorianCalendar

Calendar calendar=Calendar.getInstance();
Calendar gCalnedar=GregorianCalendar.getInstance();

java.time.LocaleDateTime

LocalDateTime localDateTime=LocalDateTime.now();

java.time.LocaleDate

LocalDate localDate=LocalDate.now();

java.time.LocaleTime

LocalTime localTime=LocalTime.now();

 

Complete Example

package com.date;

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

public class CurrentDateandTimeExample {

public static void main(String[] args) {

SimpleDateFormat dateTimeFormat=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
//java.util.Date
Date date=new Date();
System.out.println("java.util.date Current Date and Time :"+ dateTimeFormat.format(date));

//java.util.Calendar and java.util.GregorianCalendar
Calendar calendar=Calendar.getInstance();
System.out.println("java.util.Calendar Current Date and Time :"+ dateTimeFormat.format(calendar.getTime()));
Calendar gCalendar=GregorianCalendar.getInstance();
System.out.println("java.util.GregorianCalendar Current Date and Time :"+ dateTimeFormat.format(gCalendar.getTime()));

//Java 8
//java.time.LocalDateTime
DateTimeFormatter dateTimeFormatter=DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");
LocalDateTime lDateTime=LocalDateTime.now();
System.out.println("java.time.LocalDateTime Current Date and Time :"+ dateTimeFormatter.format(lDateTime));

//java.time.LocalDate
DateTimeFormatter dateFormatter=DateTimeFormatter.ofPattern("yyyy/MM/dd");
LocalDate lDate=LocalDate.now();
System.out.println("java.time.LocalDate Current :"+ dateFormatter.format(lDate));

//java.time.LocalTime
DateTimeFormatter timeFormatter=DateTimeFormatter.ofPattern("HH:mm:ss");
LocalTime lTime=LocalTime.now();
System.out.println("java.time.LocalTime Current Time :"+ timeFormatter.format(lTime));

	}

}

Output


java.util.date Current Date and Time :2018/08/03 12:12:26
java.util.Calendar Current Date and Time :2018/08/03 12:12:26
java.util.GregorianCalendar Current Date and Time :2018/08/03 12:12:26
java.time.LocalDateTime Current Date and Time :2018/08/03 12:12:26
java.time.LocalDate Current :2018/08/03
java.time.LocalTime Current Time :12:12:26

Java : System Time and Calculate Processing Time


System.currentTimeMillis(): Returns the current time in milliseconds. The granularity of the value depends on the underlying operating system and may be larger. For example, many operating systems measure time in units of tens of milliseconds.

System.nanoTime(): Returns the current value of the running Java Virtual Machine’s high-resolution time source, in nanoseconds. This method can only be used to measure elapsed time and is not related to any other notion of system or wall-clock time.

These methods are used to calculate processing time or performance of operations , methods and apis.

Processing Time = End Time – Start Time ;

Check below example to calculate processing time :

Example

public class SystemTimeCalculation {

public static void main(String[] args) {
long startTime=System.currentTimeMillis();
long nanoStartTime=System.nanoTime();
int i=0;
while(i++<1000000);
long endTime=System.currentTimeMillis();
long nanoEndTime=System.nanoTime();
System.out.println("System Calculate Time:"+(endTime-startTime));
System.out.println("System Calculate Nano Time:"+(nanoEndTime-nanoStartTime));

}

}

Output


System Calculate Time:2
System Calculate Nano Time:1888263

Java : Sort Date Time Object and Text Format


In Java classes java.util.Date, java.util.Calendar, java.time.LocalDateTime, java.time.LocalDate and java.time.LocalTime  implements comparable interface for natural ordering. We can sort these objects implements comparable interface as below


Collections.sort(List <Date> list);
Collections.sort(List <Calendar> list);
Collections.sort(List <LocalDateTime> list);
Collections.sort(List <LocalDate> list);
Collections.sort(List <LocalTime> list);

Problem occurred when date in String format and following different format pattern as MM/dd/yyyy ‘@’hh:mm a or some more patterns then you have to implement Comparator to sort this date text patterns as below


Collections.sort(dateStrList, new Comparator() {
DateFormat f = new SimpleDateFormat("MM/dd/yyyy '@'hh:mm a"); 

@Override public int compare(String o1, String o2) {
 try { 
   return f.parse(o1).compareTo(f.parse(o2)); 
} catch (ParseException e) { 
throw new IllegalArgumentException(e); 
} 
} }); 

To learn Comparator and Comparable check below link.

Java : Comparable Vs Comparator

In below example you will see sorting of date  in text format and objects also.

Example

package com.date;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

public class SortingDateTime {
public static void main(String[] args) {
//java 8
sortDateTimeObject();
sortDateTimeText();
}

private static void sortDateTimeObject() {
List<LocalDateTime>dateTimeList=new ArrayList<LocalDateTime>();
dateTimeList.add(LocalDateTime.of(2012, 05, 12, 5, 16));
dateTimeList.add(LocalDateTime.of(2014, 03, 23, 11, 26));
dateTimeList.add(LocalDateTime.of(2011, 02, 13, 9, 36));
dateTimeList.add(LocalDateTime.of(2013, 11, 12, 5, 16));
dateTimeList.add(LocalDateTime.of(2017, 8, 11, 21, 26));
dateTimeList.add(LocalDateTime.of(2016, 9, 05, 19, 36));

DateTimeFormatter dateTimeFormatter = DateTimeFormatter
				.ofPattern("MM/dd/yyyy '@'hh:mm a");
System.out.println("---> Date & Time Object List Before Sort (MM/dd/yyyy '@'hh:mm a)");
for(LocalDateTime dateTime:dateTimeList)
{
System.out.println(dateTime.format(dateTimeFormatter));
}
// LocalDateTime  internally having Comparable interface no need additional Comparator
Collections.sort(dateTimeList);

System.out.println("---> Date & Time Object List After Sort (MM/dd/yyyy '@'hh:mm a)");
for(LocalDateTime dateTime:dateTimeList)
{
System.out.println(dateTime.format(dateTimeFormatter));
}

}

private static void sortDateTimeText()
{
List<String>dateStrList=new ArrayList<String>();
// MM/dd/yyyy '@'hh:mm a
dateStrList.add("01/21/2014 @03:13 PM");
dateStrList.add("01/21/2011 @04:37 PM");
dateStrList.add("01/21/2012 @10:41 AM");
dateStrList.add("01/21/2013 @10:48 AM");
dateStrList.add("01/22/2015 @06:16 AM");
dateStrList.add("01/22/2013 @06:19 AM");
dateStrList.add("01/21/2018 @05:19 PM");
dateStrList.add("01/21/2013 @05:19 PM");

System.out.println("---> Date & Time List Before Sort (MM/dd/yyyy '@'hh:mm a)");
for(String dateStr:dateStrList)
System.out.println(dateStr);

//Sort String Date
Collections.sort(dateStrList, new Comparator<String>() {
DateFormat f = new SimpleDateFormat("MM/dd/yyyy '@'hh:mm a");
@Override
public int compare(String o1, String o2) {
try {
  return f.parse(o1).compareTo(f.parse(o2));
    } catch (ParseException e) {
      throw new IllegalArgumentException(e);
    }
}
});

System.out.println("---> Date & Time List After Sort (MM/dd/yyyy '@'hh:mm a)");
for(String dateStr:dateStrList)
System.out.println(dateStr);
 }
}

Output


---> Date & Time Object List Before Sort (MM/dd/yyyy '@'hh:mm a)
05/12/2012 @05:16 AM
03/23/2014 @11:26 AM
02/13/2011 @09:36 AM
11/12/2013 @05:16 AM
08/11/2017 @09:26 PM
09/05/2016 @07:36 PM
---> Date & Time Object List After Sort (MM/dd/yyyy '@'hh:mm a)
02/13/2011 @09:36 AM
05/12/2012 @05:16 AM
11/12/2013 @05:16 AM
03/23/2014 @11:26 AM
09/05/2016 @07:36 PM
08/11/2017 @09:26 PM
---> Date & Time List Before Sort (MM/dd/yyyy '@'hh:mm a)
01/21/2014 @03:13 PM
01/21/2011 @04:37 PM
01/21/2012 @10:41 AM
01/21/2013 @10:48 AM
01/22/2015 @06:16 AM
01/22/2013 @06:19 AM
01/21/2018 @05:19 PM
01/21/2013 @05:19 PM
---> Date & Time List After Sort (MM/dd/yyyy '@'hh:mm a)
01/21/2011 @04:37 PM
01/21/2012 @10:41 AM
01/21/2013 @10:48 AM
01/21/2013 @05:19 PM
01/22/2013 @06:19 AM
01/21/2014 @03:13 PM
01/22/2015 @06:16 AM
01/21/2018 @05:19 PM

Java : Sort Date Object and Text Format


In Java classes java.util.Date, java.util.Calendar, java.time.LocalDateTime, java.time.LocalDate and java.time.LocalTime  implements comparable interface for natural ordering. We can sort these objects implements comparable interface as below


Collections.sort(List <Date> list);
Collections.sort(List <Calendar> list);
Collections.sort(List <LocalDateTime> list);
Collections.sort(List <LocalDate> list);
Collections.sort(List <LocalTime> list);

Problem occurred when date in String format and following different format pattern as dd/MM/yyyy or some more patterns then you have to implement Comparator to sort this date text patterns as below


Collections.sort(dateStrList, new Comparator() {
DateFormat f = new SimpleDateFormat("dd/MM/yyyy"); 

@Override public int compare(String o1, String o2) {
 try { 
   return f.parse(o1).compareTo(f.parse(o2)); 
} catch (ParseException e) { 
throw new IllegalArgumentException(e); 
} 
} }); 

To learn Comparator and Comparable check below link.

Java : Comparable Vs Comparator

In below example you will see sorting of date  in text format and objects also.

Example

package com.date;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

public class SortingDate {

public static void main(String[] args) {
 //java 8
sortDateObject();
sortDateText();
}

private static void sortDateText() {
List<String>dateStrList=new ArrayList<String>();
// Date format dd/MM/YYYY
dateStrList.add("03/04/2016");
dateStrList.add("03/01/2016");
dateStrList.add("02/06/2016");
dateStrList.add("02/09/2016");
dateStrList.add("22/09/2016");
dateStrList.add("16/09/2016");
dateStrList.add("03/04/2016");
dateStrList.add("01/08/2017");
dateStrList.add("02/06/2017");
dateStrList.add("02/09/2014");
dateStrList.add("22/09/2018");
dateStrList.add("16/09/2015");

System.out.println("---> Date List Before Sort (dd/MM/yyyy)");
for(String dateStr:dateStrList)
System.out.println(dateStr);

//Sort String Date
Collections.sort(dateStrList, new Comparator<String>() {
DateFormat f = new SimpleDateFormat("dd/MM/yyyy");
    @Override
    public int compare(String o1, String o2) {
    try {
	 return f.parse(o1).compareTo(f.parse(o2));
	} catch (ParseException e) {
	       throw new IllegalArgumentException(e);
	   }
	   }
	});

    System.out.println("---> Date List After Sort (dd/MM/yyyy)");
    for(String dateStr:dateStrList)
    System.out.println(dateStr);

	}

private static void sortDateObject() {
List<LocalDate>dateList=new ArrayList<LocalDate>();
dateList.add(LocalDate.of(2012, 05, 12));
dateList.add(LocalDate.of(2014, 03, 23));
dateList.add(LocalDate.of(2011, 02, 13));
dateList.add(LocalDate.of(2013, 11, 12));
dateList.add(LocalDate.of(2017, 8, 11));
dateList.add(LocalDate.of(2016, 9, 05));

DateTimeFormatter dateTimeFormatter = DateTimeFormatter
				.ofPattern("MM/dd/yyyy");
System.out.println("---> Date  Object List Before Sort (MM/dd/yyyy)");
for(LocalDate localDate:dateList)
{
  System.out.println(localDate.format(dateTimeFormatter));
}
// LocalDate  internally having Comparable interface no need additional Comparator
Collections.sort(dateList);

System.out.println("---> Date Object List After Sort (MM/dd/yyyy)");
for(LocalDate localDate:dateList)
{
System.out.println(localDate.format(dateTimeFormatter));
}

}

}

Output


---> Date  Object List Before Sort (MM/dd/yyyy)
05/12/2012
03/23/2014
02/13/2011
11/12/2013
08/11/2017
09/05/2016
---> Date Object List After Sort (MM/dd/yyyy)
02/13/2011
05/12/2012
11/12/2013
03/23/2014
09/05/2016
08/11/2017
---> Date List Before Sort (dd/MM/yyyy)
03/04/2016
03/01/2016
02/06/2016
02/09/2016
22/09/2016
16/09/2016
03/04/2016
01/08/2017
02/06/2017
02/09/2014
22/09/2018
16/09/2015
---> Date List After Sort (dd/MM/yyyy)
02/09/2014
16/09/2015
03/01/2016
03/04/2016
03/04/2016
02/06/2016
02/09/2016
16/09/2016
22/09/2016
02/06/2017
01/08/2017
22/09/2018

Java: Date to GregorianCalendar and GregorianCalendar to Date Conversion


java.util.GregorianCalendar is a concrete subclass of GregorianCalendar this provides the standard calendar system used by most of the world. GregorianCalendarsupports Julian and Gregorian calendar systems with the support.

Gregorian calendar was instituted (October 15, 1582 in some countries, later in others). The cutover date may be changed by the caller by calling setGregorian

The only difference between the Gregorian and the Julian calendar is the leap year rule. The Julian calendar specifies leap years every four years, whereas the Gregorian calendar omits century years which are not divisible by 400.

GregorianCalendar getInstance method returns a Calendar object whose calendar fields have been initialized with the current date and time:

Calendar calendar= GregorianCalendar.getInstance();

In below example you will see conversion from Java Date object to Calendar and Calendar to Java Date.

Example

package com.date;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

public class DateString2GregarianCalendarConversion {
private static final String MAIN_DATE="2012-02-16T18:30:00.000Z";
private static final String MAIN_DATE_FORMAT="yyyy-MM-dd'T'hh:mm:ss.SSS'Z'";
private static final String DATE_FORMAT1="yyyyMMdd";
public static void main(String[] args) {
SimpleDateFormat formatter=new SimpleDateFormat(MAIN_DATE_FORMAT);
try
{
Date date =formatter.parse(MAIN_DATE);
//convert date date to calendar
Calendar calendar=GregorianCalendar.getInstance();
calendar.setTime(date);
System.out.println(calendar);

//convert GregorianCalendar to date
Date newDate=calendar.getTime();
formatter=new SimpleDateFormat(DATE_FORMAT1);
System.out.println(formatter.format(newDate));

}
catch(ParseException ex)
{
ex.printStackTrace();
}
}
}

 

Output


java.util.GregorianCalendar[time=1329445800000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="America/Los_Angeles",offset=-28800000,dstSavings=3600000,useDaylight=true,transitions=185,lastRule=java.util.SimpleTimeZone[id=America/Los_Angeles,offset=-28800000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2012,MONTH=1,WEEK_OF_YEAR=7,WEEK_OF_MONTH=3,DAY_OF_MONTH=16,DAY_OF_YEAR=47,DAY_OF_WEEK=5,DAY_OF_WEEK_IN_MONTH=3,AM_PM=1,HOUR=6,HOUR_OF_DAY=18,MINUTE=30,SECOND=0,MILLISECOND=0,ZONE_OFFSET=-28800000,DST_OFFSET=0]
20120216

Reference

https://docs.oracle.com/javase/8/docs/api/java/util/GregorianCalendar.html

Java: Date to Calendar Conversion


The java.util.Calendar class that provides methods for converting between a specific instant in time and a set of calendar fields such as YEAR, MONTH, DAY_OF_MONTH, HOUR, and so on, and for manipulating the calendar fields, such as getting the date of the next month. An instant in time can be represented by a millisecond value that is an offset from the Epoch, January 1, 1970 00:00:00.000 GMT (Gregorian).

Calendar’s getInstance method returns a Calendar object whose calendar fields have been initialized with the current date and time:

Calendar calendar= Calendar.getInstance();

In below example you will see conversion from Java Date object to Calendar and Calendar to Java Date.

Example

package com.date;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DateString2CalendarConversion {
private static final String MAIN_DATE="2012-02-16T18:30:00.000Z";
private static final String MAIN_DATE_FORMAT="yyyy-MM-dd'T'hh:mm:ss.SSS'Z'";
private static final String DATE_FORMAT1="yyyyMMdd";
public static void main(String[] args) {
SimpleDateFormat formatter=new SimpleDateFormat(MAIN_DATE_FORMAT);
try
{
Date date =formatter.parse(MAIN_DATE);
//convert date date to calendar
Calendar calendar=Calendar.getInstance();
calendar.setTime(date);
System.out.println(calendar);

//convert Calendar to date
Date newDate=calendar.getTime();
formatter=new SimpleDateFormat(DATE_FORMAT1);
System.out.println(formatter.format(newDate));

}
catch(ParseException ex)
{
 ex.printStackTrace();
}&lt;span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			&gt;&lt;/span&gt;
}
}

Output


java.util.GregorianCalendar[time=1329445800000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="America/Los_Angeles",offset=-28800000,dstSavings=3600000,useDaylight=true,transitions=185,lastRule=java.util.SimpleTimeZone[id=America/Los_Angeles,offset=-28800000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=2012,MONTH=1,WEEK_OF_YEAR=7,WEEK_OF_MONTH=3,DAY_OF_MONTH=16,DAY_OF_YEAR=47,DAY_OF_WEEK=5,DAY_OF_WEEK_IN_MONTH=3,AM_PM=1,HOUR=6,HOUR_OF_DAY=18,MINUTE=30,SECOND=0,MILLISECOND=0,ZONE_OFFSET=-28800000,DST_OFFSET=0]
20120216

Java : Date Format Locale and Internationalization


java.text.SimpleDateFormat class is subclass for java.text.DateFormat which use to convert String to Date and Date to different formatted String. DateFormat class supports TimeZone and Locale specific conversions also.

In below example you will see conversion of date to different Internationalization date by using Locale in SimpleDateFormat.

Example

package com.date;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
public class DateFormatLocaleAndInternationalization {

// Date formats
private static final String MAIN_DATE_FORMAT = "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'";

public static void main(String[] args) {

Date date = new Date();
System.out.println("Date:" + date);

SimpleDateFormat formatter = new SimpleDateFormat(MAIN_DATE_FORMAT, Locale.CANADA);
System.out.println("CANADA DATE :" + formatter.format(date));

formatter = new SimpleDateFormat(MAIN_DATE_FORMAT, Locale.CANADA_FRENCH);
System.out.println("CANADA FRENCH DATE :" + formatter.format(date));

formatter = new SimpleDateFormat(MAIN_DATE_FORMAT, Locale.GERMANY);
System.out.println("GERMANY :" + formatter.format(date));

formatter = new SimpleDateFormat(MAIN_DATE_FORMAT, Locale.CHINESE);
System.out.println("CHINESE DATE :" + formatter.format(date));
formatter = new SimpleDateFormat(MAIN_DATE_FORMAT, Locale.ITALIAN);
System.out.println("ITALIAN :" + formatter.format(date));

formatter = new SimpleDateFormat(MAIN_DATE_FORMAT, Locale.TAIWAN);
System.out.println("TAIWAN DATE :" + formatter.format(date));

	}

}

Output


Date:Mon Jul 30 12:01:13 PDT 2018
CANADA DATE :2018-07-30T12:01:13.909Z
CANADA FRENCH DATE :2018-07-30T12:01:13.909Z
GERMANY :2018-07-30T12:01:13.909Z
CHINESE DATE :2018-07-30T12:01:13.909Z
ITALIAN :2018-07-30T12:01:13.909Z
TAIWAN DATE :2018-07-30T12:01:13.909Z

Java : java.util.Date and java.sql.Timestamp Conversion


java.sql.Timestamp is subclass of java.util.Date. java.sql.Timestamp  allows the JDBC API to identify this as an SQL TIMESTAMP value. It hold the SQL TIMESTAMP fractional seconds value, by allowing the specification of fractional seconds to a precision of nanoseconds. A Timestamp also provides formatting and parsing operations to support the JDBC escape syntax for timestamp values.
The precision of a Timestamp object is calculated to be either:
Precision 19 :yyyy-mm-dd hh:mm:ss
Precision 20 + s: yyyy-mm-dd hh:mm:ss.[fff…] and s represents the scale of the given Timestamp, its fractional seconds precision.

Note: This type is a composite of a java.util.Date and a separate nanoseconds value. Only integral seconds are stored in the java.util.Date component. The fractional seconds – the nanos – are separate.

Example

package com.date;

import java.sql.Timestamp;
import java.util.Date;

public class DateSQLTimestampConversion {

public static void main(String[] args) {
Date date = new Date();
System.out.println("Java Util Date : " + date);

// Converting java.util.Date to java.sql.Timestamp
Timestamp timestamp = new Timestamp(date.getTime());
System.out.println("Java SQL Timestamp : " + timestamp);

// Convert java.sql.Timestamp to java.util.Date
Date newDate = new Date(timestamp.getTime());
System.out.println("Java Util Date : " + date);
}

}

Output


Java Util Date : Mon Jul 30 11:35:27 PDT 2018
Java SQL Timestamp : 2018-07-30 11:35:27.246
Java Util Date : Mon Jul 30 11:35:27 PDT 2018

Reference

https://docs.oracle.com/javase/8/docs/api/java/sql/Timestamp.html

Java : java.util.Date and java.sql.Date Conversion


java.sql.Date a thin wrapper around a millisecond value that allows JDBC to identify this as an SQL DATE around. A milliseconds value represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT (epoch). java.sql.Date instance normalized by settings hours, minute, seconds and milliseconds to zero.

Note: java.sql.Date have only Date part no any time related values.

Example

package com.date;

import java.util.Date;

public class DateSQLConversion {

public static void main(String[] args) {
java.util.Date utilDate = new java.util.Date();
System.out.println("Java Util Date : " + utilDate);

// contains only date information without time
java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
System.out.println("Java SQL Date : " + sqlDate); 

//convert sql date to java date
java.util.Date newUtilDate=new Date(sqlDate.getTime());
System.out.println("Java Util Date : " + newUtilDate);
}

}

Output


Java Util Date : Mon Jul 30 11:06:20 PDT 2018
Java SQL Date : 2018-07-30
Java Util Date : Mon Jul 30 11:06:20 PDT 2018

Java : Date Format Conversion


In below example for Date format conversion. Here are characters use for date format design.

[table id=6 /]

 Example

package com.date;

import java.text.SimpleDateFormat;
import java.util.Date;

public class DateFormatChange {

public static String LEGACY_FORMAT = "EEE MMM dd hh:mm:ss zzz yyyy";
public static String ISO_FORMAT = "yyyy-MM-dd'T'HH:mm:ss.SSS zzz";
public static String SHORT_DATE = "yyyy-MM-dd";
private static final String MAIN_DATE_FORMAT = "yyyy-MM-dd'T'hh:mm:ss.SSS'Z'";
private static final String DATE_FORMAT1 = "yyyyMMdd";
private static final String DATE_FORMAT2 = "yyyy/MM/dd";

private static final SimpleDateFormat legacyFormatter = new SimpleDateFormat(LEGACY_FORMAT);
private static final SimpleDateFormat isoFormatter = new SimpleDateFormat(ISO_FORMAT);
private static final SimpleDateFormat shortFormatter = new SimpleDateFormat(SHORT_DATE);

public static void main(String[] args) {
Date date = new Date();
System.out.println("Default Date Format:" + date);
System.out.println("Legacy Date Format:" + legacyFormatter.format(date));
System.out.println("ISO Date Format:" + isoFormatter.format(date));
System.out.println("Short Date Format:" + shortFormatter.format(date));
// Other Date Format
SimpleDateFormat formatter = new SimpleDateFormat(DATE_FORMAT1);
System.out.println("Short Date Format1:" + formatter.format(date));
formatter = new SimpleDateFormat(DATE_FORMAT2);
System.out.println("Short Date Format2:" + formatter.format(date));
formatter = new SimpleDateFormat(MAIN_DATE_FORMAT);
System.out.println("Main Date Format:" + formatter.format(date));
	}
}

Output


Default Date Format:Sat Jul 28 13:35:28 PDT 2018
Legacy Date Format:Sat Jul 28 01:35:28 PDT 2018
ISO Date Format:2018-07-28T13:35:28.245 PDT
Short Date Format:2018-07-28
Short Date Format1:20180728
Short Date Format2:2018/07/28
Main Date Format:2018-07-28T01:35:28.245Z

Date pattern Examples

[table id=7 /]

Java : Date Format and TimeZone Conversion


In below example convert date to different date format and time zone.

Example

package com.date;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;

public class Date2TimeZoneUtility {
public static String LEGACY_FORMAT = "EEE MMM dd hh:mm:ss zzz yyyy";
public static String ISO_FORMAT = "yyyy-MM-dd'T'HH:mm:ss.SSS zzz";
public static String SHORT_DATE = "yyyy-MM-dd";
private static final TimeZone utc = TimeZone.getTimeZone("UTC");
private static final SimpleDateFormat legacyFormatter = new SimpleDateFormat(LEGACY_FORMAT);
private static final SimpleDateFormat isoFormatter = new SimpleDateFormat(ISO_FORMAT);
private static final SimpleDateFormat shortFormatter = new SimpleDateFormat(SHORT_DATE);
static {
		legacyFormatter.setTimeZone(utc);
		isoFormatter.setTimeZone(utc);
	}

public static void main(String[] args) {
Date2TimeZoneUtility dateUtility = new Date2TimeZoneUtility();
Date date = new Date();
System.out.println("Current Date Default:" + date);
System.out.println("--->Change Date Format");
// Legacy Date Format
System.out.println("Current Date Legacy Format:" + dateUtility.toString(date, legacyFormatter));
// ISO Date Format
System.out.println("Current Date ISO Format:" + dateUtility.toString(date, isoFormatter));
// Short Date Format
System.out.println("Current Date Short Format:" + dateUtility.toString(date, shortFormatter));
System.out.println("--->Change Date Format with Time Zone UTC");
// Legacy Date Format UTC TimeZone
System.out.println("Current Date Legacy Format:" + dateUtility.toLegacyFormat(date));
// ISO Date Format UTC TimeZone
System.out.println("Current Date ISO Format:" + dateUtility.toISOFormat(date));
// Short Date Format UTC TimeZone
System.out.println("Current Date Short Format:" + dateUtility.toShortFormat(date));
System.out.println("--->Change Date Format with different Time Zone");
// Legacy Date Format with time Zone EST
System.out.println("Current Date Legacy Format with TimeZone EST:"
				+ dateUtility.toStringWithTimeZone(date, legacyFormatter, "EST"));
// ISO Date Format with time zone IST
System.out.println( "Current Date ISO Format with TimeZone IST:" + dateUtility.toStringWithTimeZone(date, isoFormatter, "IST"));
// Short Date Format Short Date with PST
System.out.println("Current Date Short Format with TimeZone PST:"
				+ dateUtility.toStringWithTimeZone(date, shortFormatter, "PST"));
}

public String toString(final Date date, SimpleDateFormat dateformat) {
		return dateformat.format(date);
}

public String toStringWithTimeZone(final Date date, SimpleDateFormat dateformat, final String timezone) {
		final TimeZone tz = TimeZone.getTimeZone(timezone);
		dateformat.setTimeZone(tz);
		return dateformat.format(date);
}

public String toLegacyFormat(final Date date) {
		return legacyFormatter.format(date);
}

public String toISOFormat(final Date date) {
		return isoFormatter.format(date);
}

public String toShortFormat(final Date date) {
		return shortFormatter.format(date);
}

}

Output


Current Date Default:Sat Jul 28 12:58:38 PDT 2018
--->Change Date Format
Current Date Legacy Format:Sat Jul 28 07:58:38 UTC 2018
Current Date ISO Format:2018-07-28T19:58:38.932 UTC
Current Date Short Format:2018-07-28
--->Change Date Format with Time Zone UTC
Current Date Legacy Format:Sat Jul 28 07:58:38 UTC 2018
Current Date ISO Format:2018-07-28T19:58:38.932 UTC
Current Date Short Format:2018-07-28
--->Change Date Format with different Time Zone
Current Date Legacy Format with TimeZone EST:Sat Jul 28 02:58:38 EST 2018
Current Date ISO Format with TimeZone IST:2018-07-29T01:28:38.932 IST
Current Date Short Format with TimeZone PST:2018-07-28

[Solved] java.time.DateTimeException: Invalid value for NanoOfSecond (valid values 0 – 999999999): -1


java.time.DateTimeException occurs while calculating Date and Time in java.time classes. This exception is used to indicate problems with creating, querying and manipulating date-time objects.

Constructors

  • DateTimeException(String message) : Constructs a new date-time exception with the specified message.
  • DateTimeException(String message, Throwable cause) : Constructs a new date-time exception with the specified message and cause.

Sample Code

 try {
// Try creating time by providing invalid inputs
LocalTime invalidTime = LocalTime.of(12, 12, 23,-1);
// Exception in thread "main" java.time.DateTimeException:
// Invalid value for nano seconds valid values(0 - 999999999): -1
} catch (DateTimeException ex) {
ex.printStackTrace();
}
 

Output Message


Exception in thread "main" java.time.DateTimeException: Invalid value for NanoOfSecond (valid values 0 - 999999999): -1
    at java.time.temporal.ValueRange.checkValidValue(Unknown Source)
    at java.time.temporal.ChronoField.checkValidValue(Unknown Source)
    at java.time.LocalTime.of(Unknown Source)
    at datetime.Java8DateTime.main(Java8DateTime.java:69)

Issue

Here with throw DateTimeException if pass as Invalidate Date and Time. For above example passing hour as 24 which causing this issue because allowed values for hours is between range 0-23.

Solutions

To solve this issue pass hour value in method as between range 0-23. Below are some methods for LocalTime.of() and corresponding values range for each parameters.

  • LocalTime localTime= LocalTime.of(int hour, int minute) : Obtains an instance of LocalTime from an hour and minute.
  • LocalTime localTime= LocalTime.of(int hour, int minute, int second): Obtains an instance of LocalTime from an hour, minute and second.
  • LocalTime localTime= LocalTime.of(int hour, int minute, int second, int nanoOfSecond): Obtains an instance of LocalTime from an hour, minute, second and nanosecond.

Date and Time Allowed range:

  • Month : 0-12
  • Day : 

1-31 : (January, March, May, July, August, October, December)

1-28/29 : (February) Leap year have 29 days in February and Non leap year have 28 days

1-30: (April, June, September, November)

  • Hour: 0-23
  • Minute: 0-59
  • Second : 0 – 59
  • NanoSecond: 0 – 999999999

Issues Solution

For more other JAVA/JDBC issues solution follow link JAVA/JDBC Issues.

[Solved] java.time.DateTimeException: Invalid value for HourOfDay (valid values 0 – 23): 24


java.time.DateTimeException occurs while calculating Date and Time in java.time classes. This exception is used to indicate problems with creating, querying and manipulating date-time objects.

Constructors

  • DateTimeException(String message) : Constructs a new date-time exception with the specified message.
  • DateTimeException(String message, Throwable cause) : Constructs a new date-time exception with the specified message and cause.

Sample Code

 try {
// Try creating time by providing invalid inputs
LocalTime invalidTime = LocalTime.of(24,18);
// Exception in thread "main" java.time.DateTimeException:
// Invalid value for HourOfDay (valid values 0 - 23): 24
} catch (DateTimeException ex) {
ex.printStackTrace();
}
 

Output Message

 


Exception in thread "main" java.time.DateTimeException: Invalid value for HourOfDay (valid values 0 - 23): 24
    at java.time.temporal.ValueRange.checkValidValue(Unknown Source)
    at java.time.temporal.ChronoField.checkValidValue(Unknown Source)
    at java.time.LocalTime.of(Unknown Source)
    at datetime.Java8DateTime.main(Java8DateTime.java:69)

Issue

Here with throw DateTimeException if pass as Invalidate Date and Time. For above example passing hour as 24 which causing this issue because allowed values for hours is between range 0-23.

Solutions

To solve this issue pass hour value in method as between range 0-23. Below are some methods for LocalTime.of() and corresponding values range for each parameters.

  • LocalTime localTime= LocalTime.of(int hour, int minute) : Obtains an instance of LocalTime from an hour and minute.
  • LocalTime localTime= LocalTime.of(int hour, int minute, int second): Obtains an instance of LocalTime from an hour, minute and second.
  • LocalTime localTime= LocalTime.of(int hour, int minute, int second, int nanoOfSecond): Obtains an instance of LocalTime from an hour, minute, second and nanosecond.

Date and Time Allowed range:

  • Month : 0-12
  • Day : 

1-31 : (January, March, May, July, August, October, December)

1-28/29 : (February) Leap year have 29 days in February and Non leap year have 28 days

1-30: (April, June, September, November)

  • Hour: 0-23
  • Minute: 0-59
  • Second : 0 – 59
  • NanoSecond: 0 – 999999999

Issues Solution

For more other JAVA/JDBC issues solution follow link JAVA/JDBC Issues.

Logstash , JDBC Input configuration tutorial with sql_last_value and tracking_column as numeric or timestamp


Logstash , JDBC Input Plug-in work like a adapter to send your database detail to Elasticsearch so that utilize for full text search, query, analysis and show in form of Charts and Dashboard to Kibana.

In below example I will explain about how to create Logstash configuration file by  using JDBC Input Plug-in for Oracle Database and output to Elasticsearch .

Logstash JDBC Input configuration for Elasticsearch Output

Pre-requisite:

Sample Data:

Below sample data is from defect_detail table where defect id as numeric value and increment continuously in ascending order.

defect_id	owned_by	severity	status	          summary	                  application	created_by	creation_date	modified_by	modified_date	assigned_to
530812		Ramesh      Severity 3	Cacelled	      Customer call 5 time 	      TEST-APP	    Saurabh	    7/3/2017 15:44	Gaurav	    8/19/2017 6:22	Development
530828	    Neha	    Severity 1	Cancelled	      Dealer Code Buyer on behalf TEST-APP-5	Rajan	    7/3/2017 16:20	Nilam	    8/17/2017 9:29	Development
540829	    Ramesh	    Severity 1	Retest Completed  Client Not want  Bulk call  TEST-APP-4	Rajiv	    7/24/2017 11:29	Raghav	    8/5/2017 20:00	IST

Configuration File :

Below configuration file is setup to read data from Oracle database , will execute query in every 15 minute and read records after last run value of defect id . We should always use order by for column for which need to use last run value as configured for defect_id having numeric column.

If you are using any other database like MYSQL, SQLServer, DB2 etc. change jdbc_driver_library and jdbc_connection_string according to database. Because every database have there own query format so update query accordinly.

Copy below content and create file in bin directory as /bin/logstash-jdbc-defect.conf

input
{
jdbc {
#Path to download jdbc deriver and add in class path
jdbc_driver_library ="../jar/ojdbc6.jar";
# ORACLE Driver Class
jdbc_driver_class ="Java::oracle.jdbc.driver.OracleDriver";
# ORACLE database jdbc connection string ,  jdbc:oracle:thin:@hostname:PORT/SERVICE
jdbc_connection_string ="jdbc:oracle:thin:@hostname:1521/service";
#The user and password to connect to database
jdbc_user ="username";
jdbc_password ="password";
#Use when need to read password from file
#jdbc_password_filepath ="/opt/app/password-path-location";
jdbc_paging_enabled ="true";
jdbc_page_size ="50000";
#Configure Cron to How frequent want execute query in database
schedule ="*/15 * * * *";
#Use below if query is big and want to store in separate file
#statement_filepath ="../query/remedy-tickets-details.sql"
#Use for Inline query and if want to execute record after last run compare with value sql_last_value that can be numeric or timestamp
statement ="select defect_id,owned_by,severity,status,summary,application,created_by,creation_date,modified_by,modified_date,assigned_to from defect_detail where defect_id>:sql_last_value order by defect_id"
#Below is configuration when want to use last run value
clean_run=true
use_column_value =true
tracking_column =defect_id
#Logstash by default consider last_sql_value as numeric if it's timestamp configure specifically as timestamp
#tracking_column_type ="timestamp"
record_last_run =true
#This file keep record of sql_last_value so that when next time query run can utilize last run values
last_run_metadata_path ="logstash_jdbc_last_run_t_data.txt"
#Define type of data from database
type ="t-data"
#Configure Timestamp according to database location
#jdbc_default_timezone ="UTC";

}
}
filter
{
#To map your creation_date column with elasticsearch @timestamp use below Date filter
mutate
{
convert =[ "creation_date", "string" ]
}
#Date pattern represent to date filter this creation_date is on format "MM/dd/yyyy HH:mm"
#and from timezone America/New_York so that when store in elasticsearch in UTC will adjust accordingly

date {
match =["creation_date","MM/dd/yyyy HH:mm"]
timezone ="America/New_York"
}
}
output
{
#output to elasticsearch
elasticsearch {
index = "defect-data-%{+YYYY.MM}"
hosts = ["elasticsearch-server:9200"]
document_type = "t-type"
#Use document_id in elasticsearch id you want to stop duplicate record in elasticsearch
document_id = "%{defect_id}"
}
#Output to console
stdout { codec = rubydebug}
}

I try to give  descriptive information in comment corresponding to each properties in configuration file. if need to go in depth and  more information just drop comments and send email will discuss in detail.

Date Filter : This filter will map CREATION_DATE  to @timestamp value for Index for each document and it says to CREATION_DATE is having pattern as “MM/dd/yyyy HH:mm” so that while converting to timestamp will follow same.

Execution :

 [logstash-installation-dir]/bin/logstash -f transaction-jdbc-defect.conf

For learning validation and start Logstash with other option follow link Logstash Installation, Configuration and Start

Logstash Console Output

If you noticed by using Date filter index @timestamp value is generating based on value of CREATION_DATE and for elasticsearch output configuration for index name defect-data-%{+YYYY.MM} will create  indexes for every month based on @timestamp value as   defect-data-2017.07 for sample data and if data changing in your database and defect id increase you will see changes on your console for new defects in every 15 minute as setup in configuration file.

Result :

select defect_id,owned_by,severity,status,summary,application,created_by,creation_date,modified_by,modified_date,assigned_to from defect_detail where defect_id:sql_last_value order by defect_id"
#
{
               "severity" = "Severity 3",
                "summary" = "Customer call 5 time but no response",
               "owned_by" = "Ramesh",
          "creation_date" = "7/3/2017 15:44",
          "modified_date" = "8/19/2017 6:22",
                   "type" = "t-data",
             "created_by" = "Saurabh",
             "@timestamp" = 2017-07-03T19:44:00.000Z,
            "modified_by" = "Gaurav",
               "@version" = "1",
              "defect_id" = 530812,
            "application" = "TEST-APP",
                 "status" = "Cancelled",
            "assigned_to" = "Development"
}
{
               "severity" = "Severity 1",
                "summary" = "Dealer Code Buyer on behalf",
               "owned_by" = "Neha",
          "creation_date" = "7/3/2017 16:20",
          "modified_date" = "8/17/2017 9:29",
                   "type" = "t-data",
             "created_by" = "Rajan",
             "@timestamp" = 2017-07-03T20:20:00.000Z,
            "modified_by" = "Nilam",
               "@version" = "1",
              "defect_id" = 530828,
            "application" = "TEST-APP5",
                 "status" = "Cancelled",
            "assigned_to" = "Development"
}
{
               "severity" = "Severity 1",
                "summary" = "Client Not want  Bulk call",
               "owned_by" = "Ramesh",
          "creation_date" = "7/24/2017 11:29",
          "modified_date" = "8/5/2017 20:00",
                   "type" = "t-data",
             "created_by" = "Rajiv",
             "@timestamp" = 2017-07-24T15:29:00.000Z,
            "modified_by" = "Raghav",
               "@version" = "1",
              "defect_id" = 540829,
            "application" = "TEST-APP4",
                 "status" = "Retest Complete",
            "assigned_to" = "IST - Integrated System Test"
}

Summary

In above detail cover about below points:

  • Logstash JDBC Input from Oracle Database.
  • JDBC Input changes for sql_last_value for numeric and timestamp
  • Read password and multi-line query from separate file.
  • Date Filter to get Index Timestamp value based on fields and pattern.
  • Dynamic Index Name for each day by appending date format.
  • Duplicate insert record prevention on Elasticsearch.
  • Start Logstash on background for configuration file.
  • Send Logstash output to Elasticsearch and Console.

Read More

To read more on Logstash Configuration,Input Plugins, Filter Plugins, Output Plugins, Logstash Customization and related issues follow Logstash Tutorial and Logstash Issues.

Hope this blog was helpful for you.

Leave you feedback to enhance more on this topic so that make it more helpful for others.

Reference  :

 https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html

 

Logstash , JDBC Input with sql_last_value as numeric or timestamp Example


Logstash , JDBC Input Plug-in work like a adapter to send your database detail to Elasticsearch so that utilize for full text search, query, analysis and show in form of Charts and Dashboard to Kibana.

In below example I will explain about how to create Logstash configuration file by  using JDBC Input Plug-in for Oracle Database and output to Elasticsearch .

Pre-requisite:

  • Logstash 5.xx installed
  • Elasticsearch 5.xx installed
  • Java 7/8 Installed

Sample Data:

Below sample data is from defect_detail table where defect id as numeric value and increment continuously in ascending order.

defect_id	owned_by	severity	status	          summary	                  application	created_by	creation_date	modified_by	modified_date	assigned_to
530812		Ramesh      Severity 3	Cacelled	      Customer call 5 time 	      TEST-APP	    Saurabh	    7/3/2017 15:44	Gaurav	    8/19/2017 6:22	Development
530828	    Neha	    Severity 1	Cancelled	      Dealer Code Buyer on behalf TEST-APP-5	Rajan	    7/3/2017 16:20	Nilam	    8/17/2017 9:29	Development
540829	    Ramesh	    Severity 1	Retest Completed  Client Not want  Bulk call  TEST-APP-4	Rajiv	    7/24/2017 11:29	Raghav	    8/5/2017 20:00	IST

Configuration File :

Below configuration file is setup to read data from Oracle database , will execute query in every 15 minute and read records after last run value of defect id . We should always use order by for column for which need to use last run value as configured for defect_id having numeric column.

If you are using any other database like MYSQL, SQLServer, DB2 etc. change jdbc_driver_library and jdbc_connection_string according to database. Because every database have there own query format so update query accordinly.

Copy below content and create file in bin directory as /bin/logstash-jdbc-defect.conf

input
{
jdbc {
#Path to download jdbc deriver and add in class path
jdbc_driver_library ="../jar/ojdbc6.jar";
# ORACLE Driver Class
jdbc_driver_class ="Java::oracle.jdbc.driver.OracleDriver";
# ORACLE database jdbc connection string ,  jdbc:oracle:thin:@hostname:PORT/SERVICE
jdbc_connection_string ="jdbc:oracle:thin:@hostname:1521/service";
#The user and password to connect to database
jdbc_user ="username";
jdbc_password ="password";
#Use when need to read password from file
#jdbc_password_filepath ="/opt/app/password-path-location";
jdbc_paging_enabled ="true";
jdbc_page_size ="50000";
#Configure Cron to How frequent want execute query in database
schedule ="*/15 * * * *";
#Use below if query is big and want to store in separate file
#statement_filepath ="../query/remedy-tickets-details.sql"
#Use for Inline query and if want to execute record after last run compare with value sql_last_value that can be numeric or timestamp
statement ="select defect_id,owned_by,severity,status,summary,application,created_by,creation_date,modified_by,modified_date,assigned_to from defect_detail where defect_id>:sql_last_value order by defect_id"
#Below is configuration when want to use last run value
clean_run=true
use_column_value =true
tracking_column =defect_id
#Logstash by default consider last_sql_value as numeric if it's timestamp configure specifically as timestamp
#tracking_column_type ="timestamp"
record_last_run =true
#This file keep record of sql_last_value so that when next time query run can utilize last run values
last_run_metadata_path ="logstash_jdbc_last_run_t_data.txt"
#Define type of data from database
type ="t-data"
#Configure Timestamp according to database location
#jdbc_default_timezone ="UTC";

}
}
filter
{
#To map your creation_date column with elasticsearch @timestamp use below Date filter
mutate
{
convert =[ "creation_date", "string" ]
}
#Date pattern represent to date filter this creation_date is on format "MM/dd/yyyy HH:mm"
#and from timezone America/New_York so that when store in elasticsearch in UTC will adjust accordingly

date {
match =["creation_date","MM/dd/yyyy HH:mm"]
timezone ="America/New_York"
}
}
output
{
#output to elasticsearch
elasticsearch {
index = "defect-data-%{+YYYY.MM}"
hosts = ["elasticsearch-server:9200"]
document_type = "t-type"
#Use document_id in elasticsearch id you want to stop duplicate record in elasticsearch
document_id = "%{defect_id}"
}
#Output to console
stdout { codec = rubydebug}
}

I try to give  descriptive information in comment corresponding to each properties in configuration file. if need to go in depth and  more information just drop comments and send email will discuss in detail.

Date Filter : This filter will map CREATION_DATE  to @timestamp value for Index for each document and it says to CREATION_DATE is having pattern as “MM/dd/yyyy HH:mm” so that while converting to timestamp will follow same.

Execution :

 [logstash-installation-dir]/bin/logstash -f transaction-jdbc-defect.conf

For learning validation and start Logstash with other option follow link Logstash Installation, Configuration and Start

Logstash Console Output

If you noticed by using Date filter index @timestamp value is generating based on value of CREATION_DATE and for elasticsearch output configuration for index name defect-data-%{+YYYY.MM} will create  indexes for every month based on @timestamp value as   defect-data-2017.07 for sample data and if data changing in your database and defect id increase you will see changes on your console for new defects in every 15 minute as setup in configuration file.

Result :

select defect_id,owned_by,severity,status,summary,application,created_by,creation_date,modified_by,modified_date,assigned_to from defect_detail where defect_id:sql_last_value order by defect_id"
#
{
               "severity" = "Severity 3",
                "summary" = "Customer call 5 time but no response",
               "owned_by" = "Ramesh",
          "creation_date" = "7/3/2017 15:44",
          "modified_date" = "8/19/2017 6:22",
                   "type" = "t-data",
             "created_by" = "Saurabh",
             "@timestamp" = 2017-07-03T19:44:00.000Z,
            "modified_by" = "Gaurav",
               "@version" = "1",
              "defect_id" = 530812,
            "application" = "TEST-APP",
                 "status" = "Cancelled",
            "assigned_to" = "Development"
}
{
               "severity" = "Severity 1",
                "summary" = "Dealer Code Buyer on behalf",
               "owned_by" = "Neha",
          "creation_date" = "7/3/2017 16:20",
          "modified_date" = "8/17/2017 9:29",
                   "type" = "t-data",
             "created_by" = "Rajan",
             "@timestamp" = 2017-07-03T20:20:00.000Z,
            "modified_by" = "Nilam",
               "@version" = "1",
              "defect_id" = 530828,
            "application" = "TEST-APP5",
                 "status" = "Cancelled",
            "assigned_to" = "Development"
}
{
               "severity" = "Severity 1",
                "summary" = "Client Not want  Bulk call",
               "owned_by" = "Ramesh",
          "creation_date" = "7/24/2017 11:29",
          "modified_date" = "8/5/2017 20:00",
                   "type" = "t-data",
             "created_by" = "Rajiv",
             "@timestamp" = 2017-07-24T15:29:00.000Z,
            "modified_by" = "Raghav",
               "@version" = "1",
              "defect_id" = 540829,
            "application" = "TEST-APP4",
                 "status" = "Retest Complete",
            "assigned_to" = "IST - Integrated System Test"
}

Summary

In above detail cover about below points:

  • Logstash JDBC Input from Oracle Database.
  • JDBC Input changes for sql_last_value for numeric and timestamp
  • Read password and multi-line query from separate file.
  • Date Filter to get Index Timestamp value based on fields and pattern.
  • Dynamic Index Name for each day by appending date format.
  • Duplicate insert record prevention on Elasticsearch.
  • Start Logstash on background for configuration file.
  • Send Logstash output to Elasticsearch and Console.

Read More

To read more on Logstash Configuration,Input Plugins, Filter Plugins, Output Plugins, Logstash Customization and related issues follow Logstash Tutorial and Logstash Issues.

Hope this blog was helpful for you.

Leave you feedback to enhance more on this topic so that make it more helpful for others.

Reference  :

 https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html

Logstash , JDBC Input Plug-in Configuration Example with Oracle Database and Output to Elasticsearch


Logstash , JDBC Input Plug-in work like a adapter to send your database detail to Elasticsearch so that utilize for full text search, query, analysis and show in form of Charts and Dashboard to Kibana.

In below example I will explain about how to create Logstash configuration file by  using JDBC Input Plug-in for Oracle Database and output to Elasticsearch .

Logstash JDBC Input configuration for Elasticsearch Output

Pre-requisite:

Sample Data:

Below sample data is from defect_detail table where defect id as numeric value and increment continuously in ascending order.

defect_id	owned_by	severity	status	          summary	                  application	created_by	creation_date	modified_by	modified_date	assigned_to
530812		Ramesh      Severity 3	Cacelled	      Customer call 5 time 	      TEST-APP	    Saurabh	    7/3/2017 15:44	Gaurav	    8/19/2017 6:22	Development
530828	    Neha	    Severity 1	Cancelled	      Dealer Code Buyer on behalf TEST-APP-5	Rajan	    7/3/2017 16:20	Nilam	    8/17/2017 9:29	Development
540829	    Ramesh	    Severity 1	Retest Completed  Client Not want  Bulk call  TEST-APP-4	Rajiv	    7/24/2017 11:29	Raghav	    8/5/2017 20:00	IST

Configuration File :

Below configuration file is setup to read data from Oracle database , will execute query in every 15 minute and read records after last run value of defect id . We should always use order by for column for which need to use last run value as configured for defect_id having numeric column.

If you are using any other database like MYSQL, SQLServer, DB2 etc. change jdbc_driver_library and jdbc_connection_string according to database. Because every database have there own query format so update query accordinly.

Copy below content and create file in bin directory as /bin/logstash-jdbc-defect.conf

input
{
jdbc {
#Path to download jdbc deriver and add in class path
jdbc_driver_library => "../jar/ojdbc6.jar"
# ORACLE Driver Class
jdbc_driver_class => "Java::oracle.jdbc.driver.OracleDriver"
# ORACLE database jdbc connection string ,  jdbc:oracle:thin:@hostname:PORT/SERVICE
jdbc_connection_string => "jdbc:oracle:thin:@hostname:1521/service"
#The user and password to connect to database
jdbc_user => "username"
jdbc_password => "password"
#Use when need to read password from file
#jdbc_password_filepath => "/opt/app/password-path-location"
jdbc_paging_enabled => "true"
jdbc_page_size => "50000"
#Configure Cron to How frequent want execute query in database
schedule => "*/15 * * * *"
#Use below if query is big and want to store in separate file
#statement_filepath =>"../query/remedy-tickets-details.sql"
#Use for Inline query and if want to execute record after last run compare with value sql_last_value that can be numeric or timestamp
statement => "select defect_id,owned_by,severity,status,summary,application,created_by,creation_date,modified_by,modified_date,assigned_to from defect_detail where defect_id>:sql_last_value order by defect_id"
#Below is configuration when want to use last run value
clean_run=>true
use_column_value => true
tracking_column => defect_id
#Logstash by default consider last_sql_value as numeric if it's timestamp configure specifically as timestamp
#tracking_column_type => "timestamp"
record_last_run => true
#This file keep record of sql_last_value so that when next time query run can utilize last run values
last_run_metadata_path =>"logstash_jdbc_last_run_t_data.txt"
#Define type of data from database
type => "t-data"
#Configure Timestamp according to database location
#jdbc_default_timezone => "UTC"</code>

}
}
filter
{
#To map your creation_date column with elasticsearch @timestamp use below Date filter
mutate
{
convert => [ "creation_date", "string" ]
}
#Date pattern represent to date filter this creation_date is on format "MM/dd/yyyy HH:mm"
#and from timezone America/New_York so that when store in elasticsearch in UTC will adjust accordingly

date {
match => ["creation_date","MM/dd/yyyy HH:mm"]
timezone => "America/New_York"
}
}
output
{
#output to elasticsearch
elasticsearch {
index => "defect-data-%{+YYYY.MM}"
hosts => ["elasticsearch-server:9200"]
document_type => "t-type"
#Use document_id in elasticsearch id you want to stop duplicate record in elasticsearch
document_id => "%{defect_id}"
}
#Output to console
stdout { codec => rubydebug}
}

I try to give  descriptive information in comment corresponding to each properties in configuration file. if need to go in depth and  more information just drop comments and send email will discuss in detail.

Date Filter : This filter will map CREATION_DATE  to @timestamp value for Index for each document and it says to CREATION_DATE is having pattern as “MM/dd/yyyy HH:mm” so that while converting to timestamp will follow same.

Execution :

 [logstash-installation-dir]/bin/logstash -f <strong>transaction-jdbc-defect</strong>.conf

For learning validation and start Logstash with other option follow link Logstash Installation, Configuration and Start

Logstash Console Output

If you noticed by using Date filter index @timestamp value is generating based on value of CREATION_DATE and for elasticsearch output configuration for index name defect-data-%{+YYYY.MM} will create  indexes for every month based on @timestamp value as   defect-data-2017.07 for sample data and if data changing in your database and defect id increase you will see changes on your console for new defects in every 15 minute as setup in configuration file.

Result :

select defect_id,owned_by,severity,status,summary,application,created_by,creation_date,modified_by,modified_date,assigned_to from defect_detail where defect_id>:sql_last_value order by defect_id"
#
{
               "severity" => "Severity 3",
                "summary" => "Customer call 5 time but no response",
               "owned_by" => "Ramesh",
          "creation_date" => "7/3/2017 15:44",
          "modified_date" => "8/19/2017 6:22",
                   "type" => "t-data",
             "created_by" => "Saurabh",
             "@timestamp" => 2017-07-03T19:44:00.000Z,
            "modified_by" => "Gaurav",
               "@version" => "1",
              "defect_id" => 530812,
            "application" => "TEST-APP",
                 "status" => "Cancelled",
            "assigned_to" => "Development"
}
{
               "severity" => "Severity 1",
                "summary" => "Dealer Code Buyer on behalf",
               "owned_by" => "Neha",
          "creation_date" => "7/3/2017 16:20",
          "modified_date" => "8/17/2017 9:29",
                   "type" => "t-data",
             "created_by" => "Rajan",
             "@timestamp" => 2017-07-03T20:20:00.000Z,
            "modified_by" => "Nilam",
               "@version" => "1",
              "defect_id" => 530828,
            "application" => "TEST-APP5",
                 "status" => "Cancelled",
            "assigned_to" => "Development"
}
{
               "severity" => "Severity 1",
                "summary" => "Client Not want  Bulk call",
               "owned_by" => "Ramesh",
          "creation_date" => "7/24/2017 11:29",
          "modified_date" => "8/5/2017 20:00",
                   "type" => "t-data",
             "created_by" => "Rajiv",
             "@timestamp" => 2017-07-24T15:29:00.000Z,
            "modified_by" => "Raghav",
               "@version" => "1",
              "defect_id" => 540829,
            "application" => "TEST-APP4",
                 "status" => "Retest Complete",
            "assigned_to" => "IST - Integrated System Test"
}

Summary

In above detail cover about below points:

  • Logstash JDBC Input from Oracle Database.
  • JDBC Input changes for sql_last_value for numeric and timestamp
  • Read password and multi-line query from separate file.
  • Date Filter to get Index Timestamp value based on fields and pattern.
  • Dynamic Index Name for each day by appending date format.
  • Duplicate insert record prevention on Elasticsearch.
  • Start Logstash on background for configuration file.
  • Send Logstash output to Elasticsearch and Console.

Read More

To read more on Logstash Configuration,Input Plugins, Filter Plugins, Output Plugins, Logstash Customization and related issues follow Logstash Tutorial and Logstash Issues.

Hope this blog was helpful for you.

Leave you feedback to enhance more on this topic so that make it more helpful for others.

Reference  :

 https://www.elastic.co/guide/en/logstash/current/plugins-inputs-jdbc.html