to be honest I thought you'd use it just for the date field - to see what dates were in the database!!! ANyway - glad youi're sorted
On Thursday, 3 August 2017 07:39:00 UTC+3, David Schulz wrote: > 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.
