0

I have this input:

      startdate             end date         val1    val2
2015-10-13 07:00:02 2015-10-19 00:00:00      45      1900

in which one line specifies a date range that spans multiple days, and I want to split the range into separate time periods, each one being a subset of a day (each one on a separate line), to facilitate parallel processing of the (multi-day) range.

The output should be

2015-10-13 07:00:02 2015-10-13 23:59:59      45      1900
2015-10-14 00:00:01 2015-10-14 23:59:59      45      1900
2015-10-15 00:00:01 2015-10-15 23:59:59      45      1900
2015-10-16 00:00:01 2015-10-16 23:59:59      45      1900
2015-10-17 00:00:01 2015-10-17 23:59:59      45      1900
2015-10-18 00:00:01 2015-10-18 23:59:59      45      1900
2015-10-19 00:00:01 2015-10-19 00:00:00      45      1900

where the data after the end time (val1 and val2) are replicated on each line. 

  1. Actually the input records are coming from the hive table and the output records also will store it in split table.

Modifications:

date split is fine. need to split the val2 value also as per the split date.

if the date diff is 2 then we would split 2 rows that should be

  • row 1:

ratio= ratio of timespent 1st day (i.e. end-start on day 1) / val1

val2= ratio*val2

  • row 2:

ratio= ratio of timespent 1st day (i.e. end-start on day 2) / val1

val2 = ratio*val2

How can I script this?

  • 1
    Welcome to Unix & Linux! You might want to have a look at the formatting help in edit mode to get nicely formatted text. ;) As it stands, your question is rather unclear and hard to answer: What's the exact pattern your result should be following? Are you looking to solve this in a particular scripting language? Have you tried anything so far? – n.st Oct 21 '16 at 21:52
  • Yes, you can do all kinds of things to that file. What is your goal? – jpaugh Oct 21 '16 at 21:53
  • I've edited in my understanding of your question. Clarify if I'm wrong. But even if I'm right, I don't understand the final range: October 19th from 00:00:01 to 00:00:00? Is that really what you want output? – Wildcard Oct 22 '16 at 0:19
  • No. i didn't tried. i have worked small kind of scripts. I want some one to share the script for this. i could understand that and will elaborate for my requirement.@n.st – AAA Oct 22 '16 at 6:22
  • yes today's start date HH:MM:SS+1 as yesterday's end date HH:MM:SS. means 2015-10-13 07:00:02 2015-10-13 23:59:59 2015-10-14 00:00:00 2015-10-14 23:59:59 the date should be split till the end dtae@Wildcard – AAA Oct 22 '16 at 6:32
0

This script will do what you want (if I understand your requirements correctly).  I took the liberty of extrapolating your specification to allow the input to have one header line and then any number of lines with date/time ranges.  I’ll illustrate this, and discuss it further, below.

#!/bin/sh
if IFS= read header
then
        printf "%s\n" "$header"
else
        echo 'EOF on first line!' >&2
        exit 1
fi
while read start_date start_time end_date end_time other_data           # See note, below.
do
        start_epoch=$(date +"%s" -d "$start_date $start_time")  ||  {
                echo "Error processing start date&time $start_date $start_time" >&2
                exit 1
        }
        end_epoch=$(date +"%s" -d "$end_date $end_time")  ||  {
                echo "Error processing end date&time $end_date $end_time" >&2
                exit 1
        }
        if [ "$end_epoch" -lt "$start_epoch" ]
        then
                echo "End date&time $end_date $end_time is before start date&time $start_date $start_time" >&2
                # Now what?
                continue
        fi
        ok_seq=1        # Flag: we are moving forward.
        current_date="$start_date"
        current_time="$start_time"
        while [ "$ok_seq" -ne 0 ]
        do
                # Most days end at 23:59:59.
                eod_time="23:59:59"
                eod_epoch=$(date +"%s" -d "$current_date $eod_time")  ||  {
                        # This should never happen.
                        echo "Error processing end-of-day date&time $current_date $eod_time" >&2
                        exit 1
                }
                if [ "$end_epoch" -lt "$eod_epoch" ]    # We’re passing the end of the date/time range.
                then
                        if [ "$current_date" != "$end_date" ]
                        then
                                # Sanity check -- this should not happen.
                                echo "We're finishing, but the current date is $current_date and the end date is $end_date" >&2
                        fi
                        eod_time="$end_time"
                        ok_seq=0
                fi
                                                                        # See note, below.
                printf "%s %s %s %s      %s\n" "$current_date" "$current_time" "$current_date" "$eod_time" "$other_data"
                # We could also use +"%F" for the full YYYY-mm-dd date.
                current_date=$(date +"%Y-%m-%d" -d "$current_date next day")  ||  {
                        # This shouldn’t happen.
                        echo "Error getting next day after $current_date" >&2
                        exit 1
                }
                current_time="00:00:01"
        done
done

Discussion:

  • Read the header line.  If this fails, abort the script.  If it succeeds, write the line to the output.  If (as your question shows) you don’t want the header in your output, remove the printf "%s\n" "$header" statement.
  • As mentioned above: loop, reading start/end/value lines from the input until we reach the end of the input (or get a fatal error).  If you don’t want to do this, remove the while, the do, and the corresponding done.
  • Read the start date, start time, end date, end time, and other data.  other_data includes everything after the end time, i.e., val1 and val2 (and all the space between them).
  • Use the date +"%s" -d "date/time string" command to convert arbitrary date/time strings to Unix “epoch times” — the number of seconds since 1970-01-01 00:00:00 (GMT).  This lets us validate input (and exit in case of error), and also gives us numbers that we can compare. (Although I suppose we could just do string comparison on values formatted as YYYY-MM-DD HH:MM:SS.)
  • If the end date/time is before the start date/time, skip this record and go to the next line.  If you’d rather do something else (like terminate) in this case, change this code.
  • Set a flag (ok_seq) that we will use to control the loop that steps through the days.  Initialize the start date/time for the first day to be the start date/time for the entire period.
  • On each output line, the start date and the end date are the same.  On most lines, the end of day (eod) time is 23:59:59.  If (same date) + 23:59:59 is greater (later) than the end-of-period date/time, then we are on the last day (output line) of the range.  Set the eod time to the end time, and set ok_seq to 0 so we will exit the loop.
  • Write a line of output, including the “other data” (val1 and val2, etc.)
  • Compute the next day’s date.  Set the start time to 00:00:01, which will appear on each output line except for the first.

Example:

$ cat input
      startdate             end date         val1    val2
2015-10-13 07:00:02 2015-10-19 00:00:00      45      1900
2015-11-01 08:30:00 2015-11-05 15:00:00      42      6083
2015-12-27 12:00:00 2016-01-04 12:34:56      17      quux

$ ./script < input
      startdate             end date         val1    val2
2015-10-13 07:00:02 2015-10-13 23:59:59      45      1900
2015-10-14 00:00:01 2015-10-14 23:59:59      45      1900
2015-10-15 00:00:01 2015-10-15 23:59:59      45      1900
2015-10-16 00:00:01 2015-10-16 23:59:59      45      1900
2015-10-17 00:00:01 2015-10-17 23:59:59      45      1900
2015-10-18 00:00:01 2015-10-18 23:59:59      45      1900
2015-10-19 00:00:01 2015-10-19 00:00:00      45      1900
2015-11-01 08:30:00 2015-11-01 23:59:59      42      6083
2015-11-02 00:00:01 2015-11-02 23:59:59      42      6083
2015-11-03 00:00:01 2015-11-03 23:59:59      42      6083
2015-11-04 00:00:01 2015-11-04 23:59:59      42      6083
2015-11-05 00:00:01 2015-11-05 15:00:00      42      6083
2015-12-27 12:00:00 2015-12-27 23:59:59      17      quux
2015-12-28 00:00:01 2015-12-28 23:59:59      17      quux
2015-12-29 00:00:01 2015-12-29 23:59:59      17      quux
2015-12-30 00:00:01 2015-12-30 23:59:59      17      quux
2015-12-31 00:00:01 2015-12-31 23:59:59      17      quux
2016-01-01 00:00:01 2016-01-01 23:59:59      17      quux
2016-01-02 00:00:01 2016-01-02 23:59:59      17      quux
2016-01-03 00:00:01 2016-01-03 23:59:59      17      quux
2016-01-04 00:00:01 2016-01-04 12:34:56      17      quux

Observe that it has no problem rolling over, not only from one month to the next, but also from one year to the next.


Note: When I wrote the above version of the script, I couldn’t figure out how to capture the white space between the end time and val1, so I was getting output that looked like

      startdate             end date         val1    val2
2015-10-13 07:00:02 2015-10-13 23:59:59 45      1900
2015-10-14 00:00:01 2015-10-14 23:59:59 45      1900
2015-10-15 00:00:01 2015-10-15 23:59:59 45      1900
                    ︙

so I “cheated”, by building the ‘right amount’ of space into the printf command (before the last %s).  But if you change the spacing in your input, the above version of the script will again produce incorrectly aligned columns.  I figured out how to fix it, although it’s a little messy.  Replace the while … dostart_epoch=… lines with:

while read start_date start_time end_date other_data
do
        # $other_data includes end_time and all the following values.
        # Break them apart:
        end_time="${other_data%%[       ]*}"
        other_data="${other_data#"$end_time"}"
        start_epoch=…

where end_time has been removed from the read command, and the characters between the brackets [ and the ] are a space and a tab.  So now other_data contains the spaces before val1.  Then change the printf to

                printf "%s %s %s %s%s\n" "$current_date" "$current_time" "$current_date" "$eod_time" "$other_data"

(note that there is no space between the fourth and fifth %s).  So now you’re done.

  • Thank you G(enious)-Man. Thanks for your effort. I am totally new of shell scripting. I didnt run this query till now. once i run i will let you know the update. once again Thank you G-Man. Regards, Anbu K – AAA Oct 24 '16 at 10:50
  • I got one modification today which is given below, calculate r1= the ratio of timespent 1st day (i.e. end-start on day 1) / totaltime – AAA Oct 24 '16 at 13:11
  • I got one modification today which is given below, if we split the single row to multiple rows depend upon the date diff. of the start date and end date. then val1 should be calculated by the following formula. val2=1900, val1=45 calculate 1st row val1= the ratio of timespent 1st day (i.e. end-start on day 1) / val2. calculate 2nd row val1= the ratio of timespent 2nd day (i.e. end-start on day 1) / val2. calculate 3rd row val1= the ratio of timespent 3rd day (i.e. end-start on day 1) / val2. till the enddate. please help me to do this @G-Man – AAA Oct 24 '16 at 13:21
  • (1) Thanks for the thanks; you’re welcome. (2) We (at Stack Exchange) typically ask that if you have a new question, you should ask a new question. This seems like a small enough change that I won’t demand that, but I will request that you edit your question to specify the additional features that you want.  Include an example or two. And be clear and unambiguous. (3) Your second and third comments are inconsistent.  The second says “calculate r1=…timespent… / totaltime”, the third says something confusing about “val1= (something) / val2”. … (Cont’d) – G-Man Oct 24 '16 at 17:51
  • (Cont’d) …  If one of them is wrong, you can delete it.  Move your mouse pointer to the end (to the right of your name and the time-of-posting), and a red circle with a white X (like ⓧ) will appear; click on that.  If they’re both right, be sure to explain that in your edit to the question. (4) ”Ping” me again with another “@” message when you’ve done that. (5) The official way to thank somebody who has answered your question is to accept his answer.  When you get a little more reputation, you will also be able to vote up good answers. – G-Man Oct 24 '16 at 17:52
0

I'm guessing you are looking to get rid of the top header line. Let's say the function you are getting this input from is called 'timefunc'. You might want to try piping timefunc's output in a cut command like this:

timefunc | cut -d$'\n' -f2

The output is now:

2015-10-13 07:00:02 2015-10-19 00:00:00      45      1900
  • my goal is to split the date.if the date difference of start date and end date is >=1. and do some calculation in val1, val 2 columns. – AAA Oct 22 '16 at 6:36
  • Hi can you tell me how to take a hive table columns as a input for this query. Thanks, AAA – AAA Oct 26 '16 at 6:13
0

you can strip the header lines from your output with grep:

inputcmd | grep -v startdate
  • Hi can you tell me how to take a hive table columns as a input for this query. Thanks, AAA – AAA Oct 26 '16 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.