Given a target date and a set of two columns (the first being a random number and the second a date in order from most recent to oldest), I want to find the number for the row with the date closest to but earlier than the target. For example, given the following:

target: 2018-12-03 19:09:56.250641


5346    2018-12-06 17:44:35.010724
6347    2018-12-05 17:50:46.475593
7284    2018-12-04 18:32:11.665405
0298    2018-12-02 22:28:04.59453
1836    2018-12-02 22:27:47.585642
6653    2018-12-02 21:26:13.942103
9274    2018-12-02 21:23:28.318704

I would want to return 0298. What is the cleanest way to do this?


Since the dates are in Y-M-D H:M:S order, they could be compared as text (lexicographical or dictionary order in layman terms).

There are three fields (columns), not two. Unless the delimiter for the first column is different than the delimiter for the other columns. As you present the delimiters as space there is no way for us to know if it is an space or a tab or something else. I'll assume three columns delimited by either space or tabs.

To solve this issue, set a variable with the value of the search date and use this command:

 s='2018-12-03 19:09:56.250641'

 awk -vs="$s" '( $2" "$3 < s ){ print $1; exit }' infile

That is:

  • Compare the concatenated values of fields 2 and 3 with the value searched.
  • When this comparison becomes true (lower)
  • Print the first column and exit.
  • Works perfectly! I'm not too familiar with awk syntax. Would you mind explaining what $2" "$3 < s is doing? I get that you're comparing something to the s variable, but I'm unsure what parts of the input are represented by $2 and $3
    – Mike S
    Dec 8 '18 at 13:42
  • Second ($2) and third ($3) column?.
    – ImHere
    Dec 8 '18 at 19:52
  • Oh, I see the problem I was having. I hadn't looked close enough at the time stamps to notice there was a space in them. So when you mentioned a third field, I thought for some weird reason awk treated the whitespace as a "field", which didn't make sense to me and threw my whole interpretation of the syntax into disarray :P
    – Mike S
    Dec 8 '18 at 20:18

I normally would do this with awk as well, but here's a pipe of text tools to achieve the same result:

$ echo "0000    $s" | sort -k2 - infile | grep -B1 "$s" | head -1 | cut -d" " -f1

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.