0

I am reading dates 'VARCHAR(14)' from an oracle table with a sql query in BASH, into a variable. I want to compare that date (eg. 20140611120520) with the system date. If the variable date is 15 minutes or older than the system date I need to kick off another procedure. This is my query:

Get_einlag_time=`sqlplus ips_osmr/ips_osmr << eof | awk ' $1 == "This" { print $2 } '
select 'This ' || DZ_EINLAG
from LAG_SPI
where C_STATUS like '%E%' ;
eof
`

$Get_einlag_time - may result in more than one date seperated by a space (eg. 20140411141231 20140605075650)

I then read the dates one by one fom this variable with a For GET_TIME in $Get_einlag_time loop, comparing them with the system date.

Now I have this date value in $GET_TIME but I cannot do a date comparison as I get 'invalid date' from this variable.

This was my latest try in convertion before I gave up to ask for expert advice :-)

echo "time read from Get_einlag_time is $GET_TIME"
EINLAG_TIME=$(date +%Y%m%d -d "$GET_TIME")
EINLAG_CHK=$(date -d "$EINLAG_TIME+15 Minutes" +%Y%m%d%H%M%S)
EINLAG_SEC=$(date -d "$EINLAG_TIME" +%s)
COMPARE_TIME=$(date +%Y%m%d%H%M%S)
COMP_SEC=$(date +%s)

The $EINLAG_SEC converted %s gives me an extremely huge and unlikely value.

Braiam
  • 1
  • 11
  • 47
  • 78
Wouter
  • 1
  • 1

1 Answers1

0

Perhaps you'd do it like this:

SELECT CAST ('This ' AS TIMESTAMP) || DZ_EINLAG AS This
from LAG_SPI
where C_STATUS like '%E%' ;

Not sure if the syntax works but you get the idea.

If it doesn't work you might as well refer to here for getting unix timestamps properly:

Convert timestamp datatype into unix timestamp Oracle

Actual probable solution:

GET_TIME=${R:0:4}/${R:4:2}/${R:6:2}\ ${R:8:2}:${R:10:2}:${R:12:2}  ## Where R is like 20140411141231
Community
  • 1
  • 1
konsolebox
  • 72,135
  • 12
  • 99
  • 105
  • I get what your're suggesting and will play around with it thx.
    The 'this' and Print$2 is used in my awk to get rid of the headers etc when the oracle query returns so have clean data.     – Wouter Jun 06 '14 at 09:48
  • and for the link you gave. It can only work when I have the date value from oracle converted into a date format recognized by unix. I still have this problem that i'm reading a string VARCHAR from oracle and have to convert that into a date – Wouter Jun 06 '14 at 11:42
  • You may try converting `20140411141231` to `2014/04/11 14:12:31` like: `R=20140411141231; D=${R:0:4}/${R:4:2}/${R:6:2}\ ${R:8:2}:${R:10:2}:${R:12:2}`. And use `"$D"` for the date. I should have examined those first sorry. I quickly assumed that it's having a different timestamp format. – konsolebox Jun 06 '14 at 11:53