[GRASS-user] update column w/ output from bash function

Ken_Mankoff · January 11, 2017, 5:54pm

Hi List,

I have a DB with a columns JD and YMD (julian day, and a varchar for a better date string format). I have a bash function for the conversion. I'm having trouble figuring out how to populate the YMD column.

Conversion function:

jul2ymd () { gdate -d "$1-01-01 +$2 days -1 day" "+%Y-%m-%d"; }

Neither db.execute nor db.select appear to work with arbitrary bash commands. I's there some way to do something similar to the following to fill the new column?

v.db.update map=DB column=YMD value=$(jul 2015 $(v.db.select map=DB column=JD))

I can imagine a scenario where I loop over every row and then use v.db.update and the VALUE option, but that seems inefficient.

Thanks,

-k.

Blumentrath_Stefan1 · January 11, 2017, 7:24pm

Hi Ken,

Why not using SQLites datetime functions directly: https://www.sqlite.org/lang_datefunc.html?
Should work in db.select or at least db.execute...

Cheres,
Stefan

-----Original Message-----
From: grass-user [mailto:grass-user-bounces@lists.osgeo.org] On Behalf Of Ken Mankoff
Sent: onsdag 11. januar 2017 18.54
To: grass-user@lists.osgeo.org
Subject: [GRASS-user] update column w/ output from bash function

Hi List,

I have a DB with a columns JD and YMD (julian day, and a varchar for a better date string format). I have a bash function for the conversion. I'm having trouble figuring out how to populate the YMD column.

Conversion function:

jul2ymd () { gdate -d "$1-01-01 +$2 days -1 day" "+%Y-%m-%d"; }

Neither db.execute nor db.select appear to work with arbitrary bash commands. I's there some way to do something similar to the following to fill the new column?

v.db.update map=DB column=YMD value=$(jul 2015 $(v.db.select map=DB column=JD))

I can imagine a scenario where I loop over every row and then use v.db.update and the VALUE option, but that seems inefficient.

Thanks,

-k.

_______________________________________________
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user

hellik · January 12, 2017, 2:48pm

Neither db.execute nor db.select appear to work with arbitrary bash

commands.

AFAIK these modules need a SQL statement, not a bash command; e.g. [1]:
db.execute - Executes any SQL statement.

[1] https://grass.osgeo.org/grass73/manuals/db.execute.html

-----
best regards
Helmut
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/update-column-w-output-from-bash-function-tp5303075p5303204.html
Sent from the Grass - Users mailing list archive at Nabble.com.

hellik · January 12, 2017, 2:56pm

Helmut Kudrnovsky wrote

Neither db.execute nor db.select appear to work with arbitrary bash

commands.

AFAIK these modules need a SQL statement, not a bash command; e.g. [1]:
db.execute - Executes any SQL statement.

[1] https://grass.osgeo.org/grass73/manuals/db.execute.html

there are some sqlite Date And Time Functions:
https://www.sqlite.org/lang_datefunc.html

-----
best regards
Helmut
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/update-column-w-output-from-bash-function-tp5303075p5303206.html
Sent from the Grass - Users mailing list archive at Nabble.com.

Ken_Mankoff · January 12, 2017, 4:49pm

Hi Helmut,

I misspoke and the data column I'm working with is not Julian Day (JD) but Day of Year (DOY). I looked at the SQL datetime functions and can't find a way to convert DOY to YYY-MM-DD. Can you help with this?

Also, you are correct, these modules do not work with bash. Is there any way to populate a column with something other than SQL? I think SQL is Turing complete and therefore can be used for arbitrarily complex functions, but it doesn't seem like the right tool in this case. There are simple bash commands that can generate the data, but I have no way to get that data into the column without a for loop, which is computationally slow. My current solution is:

# extract the category (unique) and the DOY column (1 and 13)
for cat_DOY in $(db.select table=CTD_2015| cut -d"|" -f1,13); do
    # separate into category and DOY
    cat=$(echo $cat_DOY | cut -d"|" -f1)
    DOY=$(echo $cat_DOY | cut -d"|" -f2)
    YMD=$(doy2ymd 2015 ${DOY})
    v.db.update map=CTD_2015 column=YMD where="cat = ${cat}" value=${YMD}
done

Thanks,

-k.

On 2017-01-12 at 14:56, Helmut Kudrnovsky <hellik@web.de> wrote:

Helmut Kudrnovsky wrote

Neither db.execute nor db.select appear to work with arbitrary bash

commands.

AFAIK these modules need a SQL statement, not a bash command; e.g. [1]:
db.execute - Executes any SQL statement.

[1] https://grass.osgeo.org/grass73/manuals/db.execute.html

there are some sqlite Date And Time Functions:
https://www.sqlite.org/lang_datefunc.html

-----
best regards
Helmut

hellik · January 12, 2017, 5:20pm

Ken Mankoff wrote

Hi Helmut,

I misspoke and the data column I'm working with is not Julian Day (JD) but
Day of Year (DOY). I looked at the SQL datetime functions and can't find a
way to convert DOY to YYY-MM-DD. Can you help with this?

Also, you are correct, these modules do not work with bash. Is there any
way to populate a column with something other than SQL? I think SQL is
Turing complete and therefore can be used for arbitrarily complex
functions, but it doesn't seem like the right tool in this case. There are
simple bash commands that can generate the data, but I have no way to get
that data into the column without a for loop, which is computationally
slow. My current solution is:

# extract the category (unique) and the DOY column (1 and 13)
for cat_DOY in $(db.select table=CTD_2015| cut -d"|" -f1,13); do
    # separate into category and DOY
    cat=$(echo $cat_DOY | cut -d"|" -f1)
    DOY=$(echo $cat_DOY | cut -d"|" -f2)
    YMD=$(doy2ymd 2015 ${DOY})
    v.db.update map=CTD_2015 column=YMD where="cat = ${cat}" value=${YMD}
done

Thanks,

never done datetime conversions in sqlite for myself; a quick search in the
internet gives some examples for sqlite builtin conversion methods:

e.g.

https://www.sqlite.org/lang_datefunc.html
there are some examples also in there

e.g.
https://www.tutorialspoint.com/sqlite/sqlite_date_time.htm
date(timestring, modifiers...) This returns the date in this format:
YYYY-MM-DD

sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31

e.g.
http://stackoverflow.com/questions/650480/get-month-from-datetime-in-sqlite

HTH

-----
best regards
Helmut
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/update-column-w-output-from-bash-function-tp5303075p5303236.html
Sent from the Grass - Users mailing list archive at Nabble.com.

mlennert · January 12, 2017, 5:25pm

Le 12 janvier 2017 17:49:12 GMT+01:00, Ken Mankoff <mankoff@gmail.com> a écrit :

Hi Helmut,

I misspoke and the data column I'm working with is not Julian Day (JD)
but Day of Year (DOY). I looked at the SQL datetime functions and can't
find a way to convert DOY to YYY-MM-DD. Can you help with this?

Also, you are correct, these modules do not work with bash. Is there
any way to populate a column with something other than SQL? I think SQL
is Turing complete and therefore can be used for arbitrarily complex
functions, but it doesn't seem like the right tool in this case. There
are simple bash commands that can generate the data, but I have no way
to get that data into the column without a for loop, which is
computationally slow. My current solution is:

# extract the category (unique) and the DOY column (1 and 13)
for cat_DOY in $(db.select table=CTD_2015| cut -d"|" -f1,13); do
   # separate into category and DOY
   cat=$(echo $cat_DOY | cut -d"|" -f1)
   DOY=$(echo $cat_DOY | cut -d"|" -f2)
   YMD=$(doy2ymd 2015 ${DOY})
v.db.update map=CTD_2015 column=YMD where="cat = ${cat}" value=${YMD}
done

It should be possible to do this directly in SQL, but if not you might want to modify your script to write complete SQL UPDATE statements for each cat_DOY to a file (instead of the v.db.update calls) and then, once the loop is done, feed that file to db.execute. This will probably be faster.

Moritz

Thanks,

-k.

On 2017-01-12 at 14:56, Helmut Kudrnovsky <hellik@web.de> wrote:

Helmut Kudrnovsky wrote

Neither db.execute nor db.select appear to work with arbitrary bash

commands.

AFAIK these modules need a SQL statement, not a bash command; e.g.

[1]:

db.execute - Executes any SQL statement.

[1] https://grass.osgeo.org/grass73/manuals/db.execute.html

there are some sqlite Date And Time Functions:
https://www.sqlite.org/lang_datefunc.html

-----
best regards
Helmut

_______________________________________________
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user

Blumentrath_Stefan1 · January 12, 2017, 9:17pm

Hi Ken,

In SQLite it would look like this:
SELECT substr(CAST(date('2015-01-01', +CAST(DOY -1 AS text) || ' days') AS text), 6, 2);
Or
SELECT CAST(ltrim(substr(CAST(date(CAST(2015 AS text) || '-01-01', +CAST(2-1 AS text) || ' days') AS text), 6, 2), '0')as smallint)
If you want numeric return.

Thus, try:
db.select sql="SELECT substr(CAST(date('2015-01-01', +CAST(134 -1 AS text) || ' days') AS text), 6, 2);"
To see how it works...

Then try something like:
v.db.update map=CTD_2015 column=YMD value= substr(CAST(date('2015-01-01', +CAST(DOY -1 AS text) || ' days') AS text), 6, 2)
or
db.select sql="UPDATE CTD_2015 SET YMD = substr(CAST(date('2015-01-01', +CAST(134 -1 AS text) || ' days') AS text), 6, 2);"

Cheers
Stefan

-----Original Message-----
From: grass-user [mailto:grass-user-bounces@lists.osgeo.org] On Behalf Of Ken Mankoff
Sent: torsdag 12. januar 2017 17.49
To: Helmut Kudrnovsky <hellik@web.de>
Cc: grass-user@lists.osgeo.org
Subject: Re: [GRASS-user] update column w/ output from bash function

Hi Helmut,

I misspoke and the data column I'm working with is not Julian Day (JD) but Day of Year (DOY). I looked at the SQL datetime functions and can't find a way to convert DOY to YYY-MM-DD. Can you help with this?

Also, you are correct, these modules do not work with bash. Is there any way to populate a column with something other than SQL? I think SQL is Turing complete and therefore can be used for arbitrarily complex functions, but it doesn't seem like the right tool in this case. There are simple bash commands that can generate the data, but I have no way to get that data into the column without a for loop, which is computationally slow. My current solution is:

# extract the category (unique) and the DOY column (1 and 13) for cat_DOY in $(db.select table=CTD_2015| cut -d"|" -f1,13); do
    # separate into category and DOY
    cat=$(echo $cat_DOY | cut -d"|" -f1)
    DOY=$(echo $cat_DOY | cut -d"|" -f2)
    YMD=$(doy2ymd 2015 ${DOY})
    v.db.update map=CTD_2015 column=YMD where="cat = ${cat}" value=${YMD} done

Thanks,

-k.

On 2017-01-12 at 14:56, Helmut Kudrnovsky <hellik@web.de> wrote:

Helmut Kudrnovsky wrote

Neither db.execute nor db.select appear to work with arbitrary bash

commands.

AFAIK these modules need a SQL statement, not a bash command; e.g. [1]:
db.execute - Executes any SQL statement.

[1] https://grass.osgeo.org/grass73/manuals/db.execute.html

there are some sqlite Date And Time Functions:
https://www.sqlite.org/lang_datefunc.html

-----
best regards
Helmut

_______________________________________________
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user

Ken_Mankoff · January 13, 2017, 2:40am

Hi Stefan,

Your code didn't seem to work for me, but allowed me to figure out how to make it work. This does:

db.execute sql="UPDATE CTD_2015 SET YMD=date('2015-01-01', +CAST(DOY AS text) || ' days');"

I'm not sure why I can't simplify this to:

db.execute sql="UPDATE CTD_2015 SET YMD=date('2015-01-01', '+DOY days');"

But it is OK.

Thank you for the hint,

-k.

On 2017-01-12 at 21:17, Blumentrath, Stefan <Stefan.Blumentrath@nina.no> wrote:

Hi Ken,

In SQLite it would look like this:

SELECT substr(CAST(date('2015-01-01', +CAST(DOY -1 AS text) || '
days') AS text), 6, 2);

Or

SELECT CAST(ltrim(substr(CAST(date(CAST(2015 AS text) || '-01-01',
+CAST(2-1 AS text) || ' days') AS text), 6, 2), '0')as smallint)

If you want numeric return.

Thus, try:

db.select sql="SELECT substr(CAST(date('2015-01-01', +CAST(134 -1 AS
text) || ' days') AS text), 6, 2);"

To see how it works...

Then try something like:

v.db.update map=CTD_2015 column=YMD value=
substr(CAST(date('2015-01-01', +CAST(DOY -1 AS text) || ' days') AS
text), 6, 2)

or

db.select sql="UPDATE CTD_2015 SET YMD =
substr(CAST(date('2015-01-01', +CAST(134 -1 AS text) || ' days') AS
text), 6, 2);"

Cheers
Stefan

hellik · January 13, 2017, 8:42am

Ken Mankoff wrote

Hi Stefan,

Your code didn't seem to work for me, but allowed me to figure out how to
make it work. This does:

db.execute sql="UPDATE CTD_2015 SET YMD=date('2015-01-01', +CAST(DOY AS
text) || ' days');"

I'm not sure why I can't simplify this to:

db.execute sql="UPDATE CTD_2015 SET YMD=date('2015-01-01', '+DOY days');"

But it is OK.

Thank you for the hint,

-k.

On 2017-01-12 at 21:17, Blumentrath, Stefan <

Stefan.Blumentrath@

> wrote:

Hi Ken,

In SQLite it would look like this:

SELECT substr(CAST(date('2015-01-01', +CAST(DOY -1 AS text) || '
days') AS text), 6, 2);

Or

SELECT CAST(ltrim(substr(CAST(date(CAST(2015 AS text) || '-01-01',
+CAST(2-1 AS text) || ' days') AS text), 6, 2), '0')as smallint)

If you want numeric return.

Thus, try:

db.select sql="SELECT substr(CAST(date('2015-01-01', +CAST(134 -1 AS
text) || ' days') AS text), 6, 2);"

To see how it works...

Then try something like:

v.db.update map=CTD_2015 column=YMD value=
substr(CAST(date('2015-01-01', +CAST(DOY -1 AS text) || ' days') AS
text), 6, 2)

or

db.select sql="UPDATE CTD_2015 SET YMD =
substr(CAST(date('2015-01-01', +CAST(134 -1 AS text) || ' days') AS
text), 6, 2);"

Cheers
Stefan

_______________________________________________
grass-user mailing list

grass-user@.osgeo

http://lists.osgeo.org/mailman/listinfo/grass-user

Would you mind to add some notes regarding date conversion to the wiki?

https://grasswiki.osgeo.org/wiki/GRASS-Wiki

-----
best regards
Helmut
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/update-column-w-output-from-bash-function-tp5303075p5303302.html
Sent from the Grass - Users mailing list archive at Nabble.com.

Blumentrath_Stefan1 · January 13, 2017, 8:53am

You mean here: https://grasswiki.osgeo.org/wiki/SQL ?

-----Original Message-----
From: grass-user [mailto:grass-user-bounces@lists.osgeo.org] On Behalf Of Helmut Kudrnovsky
Sent: fredag 13. januar 2017 09.43
To: grass-user@lists.osgeo.org
Subject: Re: [GRASS-user] update column w/ output from bash function

Ken Mankoff wrote

Hi Stefan,

Your code didn't seem to work for me, but allowed me to figure out how
to make it work. This does:

db.execute sql="UPDATE CTD_2015 SET YMD=date('2015-01-01', +CAST(DOY
AS
text) || ' days');"

I'm not sure why I can't simplify this to:

db.execute sql="UPDATE CTD_2015 SET YMD=date('2015-01-01', '+DOY days');"

But it is OK.

Thank you for the hint,

-k.

On 2017-01-12 at 21:17, Blumentrath, Stefan <

Stefan.Blumentrath@

> wrote:

Hi Ken,

In SQLite it would look like this:

SELECT substr(CAST(date('2015-01-01', +CAST(DOY -1 AS text) || '
days') AS text), 6, 2);

Or

SELECT CAST(ltrim(substr(CAST(date(CAST(2015 AS text) || '-01-01',
+CAST(2-1 AS text) || ' days') AS text), 6, 2), '0')as smallint)

If you want numeric return.

Thus, try:

db.select sql="SELECT substr(CAST(date('2015-01-01', +CAST(134 -1 AS
text) || ' days') AS text), 6, 2);"

To see how it works...

Then try something like:

v.db.update map=CTD_2015 column=YMD value=
substr(CAST(date('2015-01-01', +CAST(DOY -1 AS text) || ' days') AS
text), 6, 2)

or

db.select sql="UPDATE CTD_2015 SET YMD =
substr(CAST(date('2015-01-01', +CAST(134 -1 AS text) || ' days') AS
text), 6, 2);"

Cheers
Stefan

_______________________________________________
grass-user mailing list

grass-user@.osgeo

http://lists.osgeo.org/mailman/listinfo/grass-user

Would you mind to add some notes regarding date conversion to the wiki?

https://grasswiki.osgeo.org/wiki/GRASS-Wiki

-----
best regards
Helmut
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/update-column-w-output-from-bash-function-tp5303075p5303302.html
Sent from the Grass - Users mailing list archive at Nabble.com.
_______________________________________________
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user

hellik · January 13, 2017, 1:25pm

You mean here: https://grasswiki.osgeo.org/wiki/SQL ?

yes, thanks.

-----
best regards
Helmut
--
View this message in context: http://osgeo-org.1560.x6.nabble.com/update-column-w-output-from-bash-function-tp5303075p5303365.html
Sent from the Grass - Users mailing list archive at Nabble.com.