Looking to export 4 tables as a CSV where the timestamps match across all tables

[MasterACE14]

Hi All,

I have 4 tables each with only 2 columns (except for one that has 3) and they all have in common a date/time field. I am trying to export all 4 tables together as one CSV file and to have the records matched together where they have the same date/time.

My tables are structured like so (which originally came from 4 individual CSV files):

Quote

 

Table: heartrate (82237 records)

Time - Value

3/01/2018 0:01 - 45

3/01/2018 0:02 - 45

3/01/2018 0:03 - 46

etc

 

Table: minuteintensities (89279 records)

ActivityMinute - Intensity

3/01/2018 0:01 -	0
3/01/2018 0:02 -	0
3/01/2018 0:03 -	0

etc

 

Table: minutesleep (27341 records)

date - value - logId

3/01/2018 0:01 -	1 -	17396451215
3/01/2018 0:02 -	1 -	17396451215
3/01/2018 0:03 -	1 -	17396451215

etc

 

Table: minutesteps (89279 records)

ActivityMinute - Steps

3/01/2018 0:01 -	0
3/01/2018 0:02 -	0
3/01/2018 0:03 -	0

etc

So as an exported CSV I would want the matching records (based on the date/time) to be part of the same record like so:

Time, heartrateValue, Intensity, minutesleepValue, minutesleepLogId, steps

3/01/2018 0:01, 45, 0, 1, 17396451215, 0

3/01/2018 0:02, 45, 0, 1, 17396451215, 0

etc.

A secondary issue is changing the date format to D/MM/YYYY from M/DD/YYYY. However, if I can get this CSV file export I can probably just loop through it in PHP and fix up the date format.

 

Thank you kindly for your assistance

 

[Barand]

You need to join the tables on the datetime columns. Make sure all the tables have a key on those columns.

        SELECT 
               DATE_FORMAT(STR_TO_DATE(msl.date, '%m/%d/%Y %k:%i'), '%e/%m/%Y %k:%i') as time
             , hr.Value as heartrate
             , mi.Intensity
             , msl.Value as minuteSleep
             , msl.LogId
             , mst.steps 
        FROM  minuteSleep msl
            JOIN
              heartrate hr
                ON msl.date = hr.Time
            JOIN
              minuteintensities mi
                ON mi.ActivityMinute = msl.date
            JOIN
              minuteSteps mst
                ON mst.ActivityMinute = msl.date

You should always store dates/datetimes as yyyy-mm-dd or yyyy-mm-dd hh:ii:ss format using DATE, DATETIME, TIMESTAMP type columns.

Note: the above assumes there will be matching records in all tables. If this is not the case, use LEFT JOIN instead of JOIN (Left joins are slower)

Edited June 28, 2018 by Barand

[MasterACE14]

Thanks Barand for the reply. I'm not sure what I'm doing wrong. When I enter the query you provided as is I get:

Quote

Error Code: 1054. Unknown column 'msl.date' in 'on clause'

Also yes it would need to be a left join as not every record will have a matching record (although the majority will).

I just want to confirm that this is the left join edit you were referring to?

SELECT 
               DATE_FORMAT(STR_TO_DATE(msl.date, '%m/%d/%Y %k:%i'), '%e/%m/%Y %k:%i') as time
             , hr.Value as heartrate
             , mi.Intensity
             , msl.Value as minuteSleep
             , msl.LogId
             , mst.steps 
        FROM  minuteSleep msl
            LEFT JOIN
              heartrate hr
                ON msl.date = hr.Time
            LEFT JOIN
              minuteintensities mi
                ON mi.ActivityMinute = msl.date
            LEFT JOIN
              minuteSteps mst
                ON mst.ActivityMinute = msl.date

EDIT: Never mind the above query with the LEFT JOINs worked. Some random character appeared at the very end of the query. It is returning 27,341 records which sounds about right. Thanks you kindly 

Edited June 28, 2018 by MasterACE14
Problem solved

[Barand]

Yes.

It primarily processes all the minuteSleep records (they are the ones with the LogId) and matches against the other tables. If the other table has no record matching on datetime then the selected column/s from that table will just contain null values