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

[Blumentrath, Stefan]

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 at lists.osgeo.org] On Behalf Of Ken Mankoff
Sent: torsdag 12. januar 2017 17.49
To: Helmut Kudrnovsky <hellik at web.de>
Cc: grass-user at 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 at 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 at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user