converting date and timestamp to to different format

Question

experts I have three column data(date, timing, extrainfo) saved in a text file named inputdata.file.

In a loop i want to read the data row wise and want to print the yearjulianday from the first column data.

secondly for the second column data I want to print the output in hour minute second format but here i want to minus 60 seconds and want to rewrite the output in same hour minute second format.Additionally, I donot want to do any operation on third column.

For example In the below attached data the first row contains 2019-01-02 07:41:26.225000 5.49.From this data at first i just want to convert 2019-01-02 to 2019+its julian day i.e. 2019002.

secondly 07:41:26.225000 should be converted to same hr:min:sec format after deduction of 60 seconds i.e 07:40:26.225000 .Similarly for other rows i want to do the same.

2019-01-02 07:41:26.225000   5.49
2019-01-05 19:43:54.185000   5
2019-01-07 19:05:21.165000   5.18

expected output

2019-01-02 07:41:26.225000   5.49   2019002  07:40:26.225000
2019-01-05 19:43:54.185000   5      2019005  19:42:54.185000   
2019-01-07 19:05:21.165000   5.18   2019007  19:04:21.165000

I tried the script as :

while read -r line;
do
   echo "$line" ;
   awk '{print $1}'
done < inputdata.file

However as i am new to shell scripting, not getting much idea on this.Please help.Thanks.

I saw this exact question earlier today, but now I can't find it. Please edit your question to include exactly what the output should be. — glenn jackman, Jul 06 '21 at 20:03
You show the exact input. Show the exact output. See [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) — glenn jackman, Jul 06 '21 at 20:09
You need to show the exact output: `2019002 07:40:26.225000 5.49` `2019005 19:42:54.185000 5` and so on, for all of your sample input. I have an answer ready to post, but I don't know if the output is correct. — glenn jackman, Jul 06 '21 at 20:21

glenn jackman · Accepted Answer · 2021-07-07T03:10:16.767

while IFS= read -r line ; do
    read -r date time extra <<<"$line"
    IFS=. read -r hms fraction <<<"$time"
    newtimestamp=$(date -d "$hms $date -60 seconds" '+%Y%j %T')
    printf '%s %s.%s\n' "$line" "$newtimestamp" "$fraction" 
done < file | column -t

column added to prettify the output: remove it if you don't need it.

I added an extra line to the output to show that you have to subtract 60 seconds from both the date and the time.

Input:

2019-01-02 07:41:26.225000   5.49
2019-01-05 19:43:54.185000   5
2019-01-07 19:05:21.165000   5.18
2020-01-01 00:00:01.0        foobar

output:

2019-01-02  07:41:26.225000  5.49    2019002  07:40:26.225000
2019-01-05  19:43:54.185000  5       2019005  19:42:54.185000
2019-01-07  19:05:21.165000  5.18    2019007  19:04:21.165000
2020-01-01  00:00:01.0       foobar  2019365  23:59:01.0

Thanks @glenn Jackman for your smart answer.It solved my problem. — manas, Jul 07 '21 at 04:35

score 0 · Answer 2 · answered Jul 06 '21 at 20:53

0

Using GNU awk for time functions and gensub():

$ cat tst.awk
BEGIN { OFS="\t" }
{
    secs = mktime(gensub(/[-:]/," ","g",$1" "$2))
    ms   = gensub(/.*\./,"",1,$2)

    print $1" "$2, $3, strftime("%Y%j",secs), strftime("%T.",secs-60) ms
}

$ awk -f tst.awk file
2019-01-02 07:41:26.225000      5.49    2019002 07:40:26.225000
2019-01-05 19:43:54.185000      5       2019005 19:42:54.185000
2019-01-07 19:05:21.165000      5.18    2019007 19:04:21.165000

answered Jul 06 '21 at 20:53

Ed Morton

188,023
17
78
185

why the extra things gets printed 1970001 05:28:59. – manas Jul 06 '21 at 21:01
What extra things? The output in my answer is exactly what you show as expected in your question and there's no `1970001 05:28:59` present. – Ed Morton Jul 06 '21 at 21:25
If you're seeing some output other than what I show, take a look at https://stackoverflow.com/questions/45772525/why-does-my-tool-output-overwrite-itself-and-how-do-i-fix-it and see if that's the issue. – Ed Morton Jul 06 '21 at 21:27
1

There are probably some empty lines in the input, so `secs` is close to zero. – glenn jackman Jul 07 '21 at 03:13
@glennjackman ah, right, that'd do it thanks for figuring that out. – Ed Morton Jul 07 '21 at 11:40

converting date and timestamp to to different format

2 Answers2