1

My task is to export data from a database with the UNLOAD TO statement in dbaccess and convert it into a file, that can be inserted into a similar database.

Now the problem:

The old database (let's call it DB1) has some dates in it stored in minutes starting from 01.01.1993. So, for example, 11.980.800 minutes would be today. Now I need to take that value and convert it to a date in the format %d.%m.%Y, so it fits in the row in the new database (DB2), which accepts only DATE as value.

The exported data from DB1 is separated by ^. I have some relevant if statements for other rows, but I just can't handle the rows with the DATE value as I wrote already. The print statement looks like this:

print "INSERT INTO zdv_voa(voa_fnr, voa_kritkz, voa_krit, voa_kz, voa_datv, voa_datb, voa_grund) VALUES("$1",\'"$2"\',\'"$3"\',\'"$4"\',"$5","$6",\'"$7"\');"

where $5 and $6 are the minutes that need to be converted to dates.

After I run the awk script, I need a file with:

INSERT INTO zdv_voa(voa_fnr, voa_kritkz, voa_krit, voa_kz, **voa_datv**, **voa_datb**, voa_grund) VALUES(1,'P','148','T','10.10.2015','02.07.1999','60');

The dates used here are just examples and the other values are already converted by me.

  • date -d '01/01/1993 +11980800 minutes' +%d.%m.%Y will help you – Costas Oct 13 '15 at 13:47
3

how many seconds elapse from epoch to Jan,1 1993 ?

date -d '01/01/1993' +%s

strftime

you can test if you awk allow strftime()

echo 11980000 | awk '{u=60*$1+725842800 ; printf "%d -> %s\n",u, strftime("%x",u); } '

this return 10/12/2015

now in your code add

dollar5=strftime("%x",60*$5+725842800) ;
# $6 is left as an exercice ..
print "INSERT INTO zdv_voa(voa_fnr, voa_kritkz, voa_krit, voa_kz, voa_datv, voa_datb, voa_grund) VALUES("$1",\'"$2"\',\'"$3"\',\'"$4"\',\'"dollar5"\',\'"dollar6"\',\'"$7"\');"

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.