Thanks. I am away now. The other little wrinkle was trying to get the interval column in the query as interval is also a keyword. using `interval` sorted that out.
On Wednesday, August 2, 2017 at 6:54:17 PM UTC+10, Andrew Milner wrote: > > 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.
