Sqlite3 export to cvs like view for all archiver records for one day

[David Bach]

In wview I regularly exported all archiver records for one day to csv. I don't see a way to do this in weewx.

I have looked at the database schema for weewx.sdb but I don't know enough to make sense of all the tables, etc.

Does anyone have a sample sql command to write one day's archive data (selecting only certain fields) to a local cvs file?

With wview I used such an export to match outside temperatures, humidity, solar radiation and ET to  corresponding values from a data logger inside a greenhouse. I haven't found a way to export weewx data for this purpose.

[Tom Keffer]

The WeeWX database consists of two parts: a main "archive" database table, and a bunch of "daily summaries," one for each type.

The main archive table is identical to wview's, so just use whatever tool you used before on it. Ignore the daily summaries.

-tk

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/2dbf1f97-2bf1-42b3-90fe-3b6292fdc55fn%40googlegroups.com.

[John Kline]

And if you are looking for a sample SQL command, the following, when run from the command line, will output dateTime, outTemp and outHumidity to yesterday.csv for all rows > 2020/11/09 00:00:00 and <= 2020/11/10 00:00:00.

You will need to adjust the paths for sqlite3 and weewx.sdb. 

/usr/bin/sqlite3 /home/weewx/archive/weewx.sdb ".mode csv" ".output yesterday.csv" "select dateTime, outTemp, outHumidity from archive where dateTime > strftime('%s','2020-11-09 00:00:00') and dateTime <= strftime('%s','2020-11-10 00:00:00')"

On Nov 10, 2020, at 6:51 AM, Tom Keffer <tke...@gmail.com> wrote:



To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEB7dmL%2Ba-70GMQMeQRgt9OR5CHu_f6t-iPN1bDB%3DQ2F3A%40mail.gmail.com.

[Karen K]

And you can write "select * from archive ..." to get all the columns of the table.

[vince]

On Tuesday, November 10, 2020 at 7:13:00 AM UTC-8, John Kline wrote:

/usr/bin/sqlite3 /home/weewx/archive/weewx.sdb ".mode csv" ".output yesterday.csv" "select dateTime, outTemp, outHumidity from archive where dateTime > strftime('%s','2020-11-09 00:00:00') and dateTime <= strftime('%s','2020-11-10 00:00:00')"

ooh - multiple commands to sqlite3 just need to be delimited by double quotes.    That is 'really' good to know.  Thanks !!!

[David Bach]

Thank you all, very much! This gave me the boost that I needed.

--

You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/ee8fee8d-f00c-4efc-af8e-4967a128208eo%40googlegroups.com.

--

Hill House Home
Snoqualmie, Washington

[David Bach]

This forum and the helpful experts supporting it are a welcome reminder of this thanksgiving season. Thanks to Tom, Vince and Karen for the help!

Here is my solution to exporting some values from archive records, in case someone might be interested:

sudo /usr/bin/sqlite3 /Users/shared/weewx/archive/weewx.sdb ".mode csv"  ".output /Users/user/Desktop/20201120.csv" "select datetime(dateTime, 'unixepoch', 'localtime'), outTemp, outHumidity, windGust, radiation, rain, ET from archive where dateTime >= strftime('%s','2020-11-20 08:00:00') and dateTime < strftime('%s','2020-11-21 08:00:00')"

I wrote the output to a Mac OS desktop, thus the sudo command.

My challenge was exporting the timestamp in MM-YY-DD HH:MM format. The unixepoch and localtime modifiers of the datetime function did that.

To get only the values for a single day I chose times 8 hours after the ones I wanted (I'm in Washington State).

The output began with values for local time 2020-11-20 00:00 and ended on 2020-11-20 23:55.

Thanks, again!

[Tom Keffer]

Glad it's working for you David.

Just one little detail: in WeeWX, a timestamp represents the end of an archive period. So, your local time day would run from 2020-11-20 00:05 through 2020-11-21 00:00.

The easiest way to do this is something like

select datetime(dateTime,'unixepoch','localtime'), outTemp, ..., where date(dateTime-300,'unixepoch','localtime')=='2020-11-21';

-tk

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAG_7EhZctWnJOngRFAYKhMGcDrXwfkFCbo6L8ZxrfA3DND5WfQ%40mail.gmail.com.