Writing shell script with sed,sql, and epoch milliseconds

Eamo · 07-16-2007, 08:04 AM

Hey

I have a bunch of .sql files that I have created. These querys will be run every month for my friendly neighborhood bean counter. Each query has two Epoch millisecond values to indicate what range of dates to count the totals from. Im trying to figure out a way to update the epoch values automagically every month before the query is executed. The dates will allways be between the first and last day of the month. I figure this is a bash scripting job where sed parses the .sql file and inserts the values in the correct place.

Im pretty much a total newbie so any suggestions to point me in the right direction (or even the solution!!

and I would be extremely grateful.

Below is a typical query for the first of june to the 30th.

select shortcode.short_code as shortcode, xml.client_config.name as client, operators.name as operator,
count(*) as del from xml.sent_messages, xml.client_config, operators, shortcode where xml.sent_messages.client_id = xml.client_config.client_id and xml.sent_messages.shortcode_id = shortcode.id and xml.sent_messages.operator_id = operators.id and shortcode.id = 4 and DELIVERY_STATUS = 1 and DELIVERY_DATE between 1180652400364 and 1183244399052 group by shortcode.short_code, xml.client_config.name, operators.name;

Ps It aint my database so I *must* use the Epoch milliseconds.

Regards

Eamo

Guttorm · 07-16-2007, 08:56 AM

Hi

If it's MySQL, it has a lot of date functions you can use to simplify the problem. Instead of using 1180652400364, you can use UNIX_TIMESTAMP('2007-06-01')*1000 which is a bit easier to read, as well as changing in a script.

MySQL works with seconds, not milliseconds, but I guess it won't matter since you want to check the month. It will also be a bit safer. I just tried this:

Code:

mysql> select FROM_UNIXTIME(1180652400364/1000);
+-----------------------------------+
| FROM_UNIXTIME(1180652400364/1000) |
+-----------------------------------+
| 2007-06-01 01:00:00               |
+-----------------------------------+

You are missing the first hour of June?

I think this will be safer:

Code:

WHERE ... AND YEAR(FROM_UNIXTIME(DELIVERY_DATE/1000))=2007 AND
MONTH(FROM_UNIXTIME(DELIVERY_DATE/1000))=6 ...

In a script, just change 2007 in $YEAR and 6 into $MONTH or something.

Hope this helps,
Guttorm.

Eamo · 07-16-2007, 09:07 AM

Its a IBM DB2 server Im afraid. I dont have any choice in using the epoch milliseconds.

I have been thinking that as a newbie maybe im making it a bit more complex than i can handle. As I said in the post my idea was the have the bash script generate the seconds and use sed to replace the values. I was thinking of trying to automate every last piece of this process as its being implemented to remove the need of a lot of cut and pasting. What if I was to manually get the millisecond value's and then just write a sed script to go through all files in a directory and where it finds the old value insert the new one.

That seems to my newbie mind like a more possible solution.

Suggestions?

Thanks again

Eamo

Guttorm · 07-16-2007, 09:21 AM

Ah, ok. It can be done in a shell as well. To convert from a date to epoch seconds, you can use the date command. Something like this:

Code:

YEAR=2007
MONTH=6
SECONDS=$(date -d "$YEAR-$MONTH-01" +"%s")

Just remember to multiply with 1000 to convert from seconds to milliseconds. I think it's safer to multiply in the SQL since I think shells don't work well with numbers biggger than 2^32.

.. DELIVERY_DATE between $SECONDS * 1000 ...

Eamo · 07-16-2007, 09:31 AM

First and foremost Guttorm thank you for you help so far. I guess I have not been explaining what Im going to do correctly.

Im thinking that I have taken a bit more than I can chew in regards to this being my first experience with sed and bash. I know how to write a sed command to go through a file and find the millisecond values from the previous month and replace them for the current month.

However I dont know how to write the bash script to automatically do this to every .sql file i have in a directory (i have them divded into sub directorys but if this makes it a lot more complicated I can get away with having them all in the one place).

Hope thats a bit more clear!

Thanks again

Eamo

Guttorm · 07-16-2007, 09:55 AM

I misunderstood. I thought you wanted to create the SQL properly with a script that takes date values as parameters or something.

To replace all occurances of a number in many files with another number, I would use Perl even if I don't know it.

I had an example in an old script. To change 12345 into 54321 in all *.sql files in a directory:

perl -pi -e 's/12345/54321/g' *.sql

Eamo · 07-16-2007, 10:22 AM

<homersimpson> how can one little line of code bring so much happiness! </homersimpson>

Now all I have to do is figure out how to get db2 to run all the sql files i have in one batch

Thanking you for all the help.

Guttorm · 07-16-2007, 03:40 PM

<dilbert>
Just remember, don't calculate with those epoch milliseconds! epoch*1000*60*60*24*7 is not next week. Your better off letting the database handle it IMO.
</dilbert>