specify the fields you are interested in instead of the * - I just found the same problem and it works if you do for example select from_unixtime(dateTime), outTemp, inTemp from archive;
with and without where 1; On Wednesday, 2 August 2017 10:23:11 UTC+3, David Schulz wrote: > Hi Andrew > > Thanks for your assistance. The query returns a syntax error. > > 17:18:44 select from_unixtime(dateTime), * from archive where 1; Error > Code: 1064. You have an error in your SQL syntax; check the manual that > corresponds to your MariaDB server version for the right syntax to use near > '* from archive where 1' at line 1 0.00039 sec > > I wasn't sure whether the ; after the 1 was required. I tried it with and > without but both returned errors. I guess the take away is that rather > than looking for a option to view the data, I'll need a query that > translates the dateTime column in the query. I'll do some more research > and hopefully nail down the right syntax. > > Thanks > > On Tuesday, August 1, 2017 at 6:05:46 PM UTC+10, Andrew Milner wrote: >> >> try >> select from_unixtime(dateTime), * from archive where 1; for MySQL >> and >> select datetime(dateTime, 'unixepoch', 'local'), * from archive where 1; >> for SQLite >> >> Should help you on the way >> >> >> >> On Tuesday, 1 August 2017 10:10:11 UTC+3, David Schulz wrote: >> >>> I seem to have weewx data spread across both a MySQL (MariaDB) database >>> as well as the local SQLite database. >>> >>> Why do I think this? I have yearly summaries going back to 2014, but >>> there are big missing patches. For example, I have data for May 2014, Jan >>> 2015, Nov, Dec 2016 and all of this year. Weewx is currently writing a >>> SQLite database on the local machine. I think I inadvertently switched to >>> SQLite in an upgrade by failing to read properly the messages about >>> applying the new config file, keeping the old etc... and reverted to the >>> default local database. >>> >>> I previously had setup weewx to write to a MySQL database on another >>> machine. I believe the 'missing' data is in that database. So the plan is >>> to merge the 2 data sources, and then reconfigure weewx to write to the >>> MySQL server. >>> >>> The problem I am having is working with the epoch unix integer date and >>> time data. I'd really like to be able to scan through the data and confirm >>> my suspicions that the MySQL data fills the missing gaps in the SQLite >>> data. I've tried half a dozen different database tools in the hope one has >>> an option to display the epoch date and time in a human readable format I >>> can work with. Anybody got any recommendations? >>> >>> Of course I've seen many, many query examples to convert a single row of >>> data from epoch to a human readable format. What I really need though is >>> to be able to scan down the table data and see what I have in each >>> location. Or maybe I am thinking about the problem the wrong way? >>> >>> Maybe a better approach is to rename the existing weewx database in >>> MySQL to avoid it being overwritten and then use wee_database --transfer to >>> at least get everything in MySQL and then work on merging the legacy data? >>> >>> Any advice from wiser heads will be appreciated. >>> >> -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
