Tuesday, 21 February 2017

subquery in mysql | update join query | mysql substring

subquery in mysql | update join query | mysql substring

case :- inserting records from one table to another (select insert)

  •  hear, i have one table named 'initial_keyword_test'. it contains data.
  •  and have another table named 'normal_keyword_test' which is empty table.
  •  both of the table haven't same table structure.
  •  but, some fields names are matching.
  •  i want to insert data in normal_keyword_test from initial_keyword_test.

Example :-   

INSERT INTO `normal_keyword_test`
(`final_keyword_id`,
`keyword`)
select
`final_keyword_id`,
`keyword`
from initial_keyword_test

Monday, 6 February 2017

crontab in linux | cron job | how to edit crontab in linux

crontab in linux | cron job | how to edit crontab in linux

The crontab is a list of commands that you want to run on a regular schedule in linux machine, and also the name of the command used to manage that list. crontab stands for "cron table," because it uses the job scheduler cron to execute tasks to given times to be executed.

Syntax :- 

MIN HOUR DAY MON DOW Command

---------------------------------------------------------------------------------------------
Field          |  Description     | Values allow                          |
---------------------------------------------------------------------------------------------
MIN           |  Minute field    |  0 to 59                                 |
HOUR        |   Hour field      |  0 to 23                                 |
DAY            |  Day of Month |    1-31                                    | 
MON          |    Month field  |    1-12                                    |
DOW          |  Day Of Week  |     0-6                                     |
Command  |   Command      |  Command to be executed.   |
---------------------------------------------------------------------------------------------

Friday, 3 February 2017

regex mysql | regex mysql query | mysql regex function

regex mysql | regex mysql query | mysql regex function


  • if you want to get the only text and numbers from varchar type field.
  • first you have to create on one function in you database.
  • than you fired query for using regexreplace();

Note :- 

this function have three argument.
Syntax :- regexreplace(regex,text ,replace text);
  1. your regex. 
  2. your text which you perform regex.
  3. your replace text.

Output :- 

----------------------------------------------------------------------------------
| input                              |   output                         |
----------------------------------------------------------------------------------
| Located (please select) |   Located please select  |
----------------------------------------------------------------------------------

Thursday, 2 February 2017

mysql date functions | date operations in mysql

mysql date functions | date operations in mysql

case :- 

  • when extract dates between  two dates.
  • When we can get dates between two dates in mysql than we can fire one single query.
  • with two parameters first is startDate and second is endDate. 
  • It display dates between this two dates under the selected_date title.

Input :-

startDate =2017-01-05
endDate   =2017-01-10

Output :- 

--------------------------
| selected_date |
--------------------------
| 2017-01-05    |
| 2017-01-06    |
| 2017-01-07    |
| 2017-01-08    |
| 2017-01-09    |
| 2017-01-10    |
--------------------------

Query :-

 

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between 'startDate' and 'endDate'

-------------------------------------------------------------------------------------------------------------------

case :- 

  • when we create dummy table based on table insert dates than we can setup select insert and dates.
  • here, you can cange date you want.

Query :-

INSERT INTO destination_table_name (col1,col2)
SELECT col1, col2
FROM source_table_name
WHERE insert_date < '2017-02-01 00:00:00';

-------------------------------------------------------------------------------------------------------------------

case :-

  • when we get time difference in two times.
  • you can just pass two difference times in this syntax.
  • you get time difference.

input format(hh:MM:ss) :-

1  09:12:30
2  08:11:25

output format(hh:MM:ss) :-

-------------------
|Difference|
-------------------
|01:00:55  |
-------------------  

Query :-


select SUBTIME('09:12:30','08:11:35');

-------------------------------------------------------------------------------------------------------------------

case :-  

  • when we get date difference in two date.
  • you have just two difference date in this syntax.

Functionality :-

--------------------------------------------------------------------
| input differences   |      output                  |
--------------------------------------------------------------------
| days                      |     X Days X Hours    |
| hours                    |    X Hours X Min       |
| Min                       |    X Min X sec           |
--------------------------------------------------------------------
Here, X is value.

Must be :- 

Format of date :- yyyy-MM-dd hh:mm:ss
Example           :- 2016-10-04 16:32:40


Input :-

--------------------------------------------------------------------------
|    start_time               |         end_time           |  
--------------------------------------------------------------------------
| 2016-10-04 16:32:40 | 2016-10-04 16:32:48 |
| 2016-10-04 16:32:41 | 2016-10-04 16:33:01 |
| 2016-10-04 16:32:40 | 2016-10-04 16:33:01 |
| 2016-10-04 16:32:51 | 2016-10-04 16:33:03 |
| 2016-10-04 16:32:49 | 2016-10-04 16:33:02 |
| 2016-10-04 16:32:54 | 2016-10-04 16:33:09 |
--------------------------------------------------------------------------

Output :-

-------------------------------------------------------------------------------------------------
|    start_time              |         end_time          |ExecutionTime|   
-------------------------------------------------------------------------------------------------
|2016-10-04 16:32:40 | 2016-10-04 16:32:48  | 0 Min 8 Sec   |
|2016-10-04 16:32:41 | 2016-10-04 16:33:01  | 0 Min 20 Sec |
|2016-10-04 16:32:40 | 2016-10-04 16:33:01  | 0 Min 21 Sec |
|2016-10-04 16:32:51 | 2016-10-04 16:33:03  | 0 Min 12 Sec |
|2016-10-04 16:32:49 | 2016-10-04 16:33:02  | 0 Min 13 Sec |
|2016-10-04 16:32:54 | 2016-10-04 16:33:09  | 0 Min 15 Sec |
------------------------------------------------------------------------------------------------

Query :-


SELECT j.start_time,j.end_time,
 CASE
                        WHEN j.start_time IS NOT NULL
                             AND j.end_time IS NOT NULL
                             THEN CASE
                                  WHEN FLOOR(HOUR(TIMEDIFF(j.end_time,j.start_time)) / 24) != 0 THEN CONCAT(FLOOR(HOUR(TIMEDIFF(j.end_time,j.start_time)) / 24), ' Days ', MOD(HOUR(TIMEDIFF(j.end_time, j.start_time)), 24), ' Hours ')
                             ELSE CASE
                                  WHEN MOD(HOUR(TIMEDIFF(j.end_time, j.start_time)), 24) != 0 THEN CONCAT(MOD(HOUR(TIMEDIFF(j.end_time, j.start_time)), 24), ' Hours ', MINUTE(TIMEDIFF(j.end_time, j.start_time)), ' Min ')
                             ELSE CONCAT(MINUTE(TIMEDIFF(j.end_time, j.start_time)), ' Min ', SECOND(TIMEDIFF(j.end_time, j.start_time)), ' Sec')
                             END
                             END
                        ELSE 'N/A'
                    END AS 'ExecutionTime'
                    FROM job_detail j;

date operations in java | java dateformat | date to string in java

date operations in java | java dateformat | date to string in java

Here,DateFiller program to get date operation related today date.

Program given to date to you should want.Program will next or previous or may both next as well as previous.

Custom Input Change :-

If you want next than + (on mark variable).
If you want previous than - (on mark variable).
If you want format change than - (on format variable).
If you want get date from particular date than change on (new Date()).


Output :- today(2016-10-14)

                                          mark value
----------------------------------------------------------------------------------------------------
    +-3            |           -+3        |        +3              |         -3             |
----------------------------------------------------------------------------------------------------
2016-10-15   |    2016-10-13   |    2016-10-15   |    2016-10-13   |
2016-10-16   |    2016-10-12   |    2016-10-16   |    2016-10-12   |
2016-10-17   |    2016-10-11   |    2016-10-17   |    2016-10-11   |
2016-10-13   |    2016-10-15   |                         |                          |
2016-10-12   |    2016-10-16   |                         |                          |   
2016-10-11   |    2016-10-17   |                         |                          |   
----------------------------------------------------------------------------------------------------

Example:-


import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;

/**
 *
 * @author vishal.khokhar
 */
public class DateFiller {

    //Here, Mark is modify your custom day date it may be diffent set
    //i.e.+-3 first next 3 day date OR previous 3 day date
    //i.e.-+3 first previous 3 day date OR next 3 day date
    //i.e.+5 first next 5 day date
    //i.e.-5 first previous 5 day date
    String mark = "-+5";

    //Here, format is modify your custom date format which you want.
    String format = "yyyy-MM-dd";

    ArrayList<String> obj = new ArrayList<String>();

    public static void main(String[] args) {
        new DateFiller().showDate();
    }

    public void showDate() {
        try {
            fillDate();
            while (!obj.isEmpty()) {
                SimpleDateFormat sdf = new SimpleDateFormat(format);
                Date date = (Date) sdf.parse(sdf.format(new Date()));
                Calendar c1 = Calendar.getInstance();
                c1.setTime(date);
                c1.add(Calendar.DATE, Integer.parseInt(obj.get(0)));
                String input = sdf.format(c1.getTime());
                obj.remove(0);
                System.out.println(input);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void fillDate() {
        try {
            LinkedHashMap lhm = new LinkedHashMap();
            while (mark != null && !mark.isEmpty()) {
                char sign = mark.charAt(0);
                if (!Character.isDigit(sign)) {
                    for (int i = 1; i <= Integer.parseInt(mark.substring(mark.length() - 1)); i++) {
                        lhm.put(sign + String.valueOf(i), sign + String.valueOf(i));
                    }
                    mark = mark.replace(String.valueOf(sign), "");
                } else if (Character.isDigit(sign)) {
                    mark = mark.replace(String.valueOf(sign), "");
                }
            }

            Set set = lhm.entrySet();
            Iterator i = set.iterator();

            while (i.hasNext()) {
                Map.Entry me = (Map.Entry) i.next();
                obj.add((String) me.getKey());
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

rotate proxy | set proxy | proxy setting | set proxy in webclient

rotate proxy | set proxy | proxy setting | set proxy in webclient

  • SomeTime site can response only particular region only than we can't response on our ip.
  • In this case we can setup environment it's look like this region.
  • you need to identify only site depends on which region.
    • than you can collect those region ip's.
  • after collect ip you can setup you ip hardcode in this program,
  • otherwise insert in database and setup dynamically in the fillProxy().

Note :-

  • Setup your request environment instead of dummy environment.
  • Setup your proxy hardcode.
  • Otherwise database.

Functionality :- 

  • If you site blocked than particular ip than program rotate dynamically to fire request other ip.
    • untile that rotate your ips which is setup by you.
  • it exit with response only this case:-
    • if that found this ip which is setup by you is belong to only this region that site may depend. AND Site given response.

Requrie library :- HtmlUnit

Requrie library :- StringUtil