MySQL database help (storing time and date)

[TonyRPH]

I have a load of comma delimited text files that have two columns of data: (time and data)

They look like this:

00:02:05,383.94

Each file is created on a specific day, using the date for the filename e.g. 29-08-2021.log

I want to store this data in a MySQL database - however I can't think of the best approach.

Obviously, I could create a database with two columns as per above, but then I lose the date stamp - so then I could create a third column e.g. date,time,data

This is on a Linux system - so what would be the best way to store the date / time? Should I use epoch format? Then of course I would only need two columns is that right? (epoch date, data)

Thanks in advance.

[droopsnoot]

I'd say one column as a datetime, and the other as whatever suits the data, a numeric type presumably.

[rxtx]

You'll need to store the data part as a subset of ISO 8601, YYYY-MM-DD. You should probably name the logfiles that way to save munging the date, they'll sort by properly too.

[TonyRPH]

I'm finally getting around to looking at this again.

I have created my database which has two columns, date_time and power.

I am using 'timestamp' for the date_time column - and storing the date like this: 2021-11-02 10:10:00

Is this correct?

 select * from Test;
+----+---------------------+-------+
| id | date_time           | power |
+----+---------------------+-------+
|  1 | 2021-11-02 10:10:00 | 35.95 |
+----+---------------------+-------+
1 row in set (0.001 sec)

EDIT corrected the data in my power column - it should have indeed been a decimal point and not a comma.

Edited by TonyRPH on Tuesday 2nd November 12:18

[bigandclever]

TonyRPH said:

Is this correct?

>consulting_hat_on

What do you want to do with it once you've stored it?

>consulting_hat_off

[anonymous-user]

I think TIMESTAMP is best
Just remember you 'may' need to convert to whatever time zone you need the data in - depends on where you database is i think.

[nyt]

TonyRPH said:

 select * from Test;
+----+---------------------+-------+
| id | date_time           | power |
+----+---------------------+-------+
|  1 | 2021-11-02 10:10:00 | 35,95 |
+----+---------------------+-------+
1 row in set (0.001 sec)

What data type have you defined the 'power' field as? It's odd that it's displaying with a comma rather than a decimal point if you're in the UK.
If you want to use it as a number (analysis, charting, etc) then store it as a number

Edited by nyt on Tuesday 2nd November 11:47

[TonyRPH]

bigandclever said:

TonyRPH said:

Is this correct?

>consulting_hat_on

What do you want to do with it once you've stored it?

>consulting_hat_off

The data will be read and presented on both graphic and tabulated format.

I will also be performing some mathematics on it to derive daily / monthly / annual figures. (this is for my energy monitor at home)

@the internet the data will be extracted with PHP which has my timezone set as appropriate. The data will only be added and read / used by me, so I don't need to allow for any other date formats.

nyt said:

TonyRPH said:

 select * from Test;
+----+---------------------+-------+
| id | date_time           | power |
+----+---------------------+-------+
|  1 | 2021-11-02 10:10:00 | 35,95 |
+----+---------------------+-------+
1 row in set (0.001 sec)

What data type have you defined the 'power' field as? It's odd that it's displaying with a comma rather than a decimal point if you're in the UK.
If you want to use it as a number (analysis, charting, etc) then store it as a number

Edited by nyt on Tuesday 2nd November 11:47

Well spotted, that's a mistake on my part, the power data is using a decimal point.

The power column is currently defined as TEXT but I see I have DECIMAL, FLOAT, DOUBLE and REAL options - I guess DECIMAL would be best here?

Thanks everyone.

[nyt]

TonyRPH said:

Well spotted, that's a mistake on my part, the power data is using a decimal point.

The power column is currently defined as TEXT but I see I have DECIMAL, FLOAT, DOUBLE and REAL options - I guess DECIMAL would be best here?

Thanks everyone.

I would use Float. Decimal would probably work for your purposes but I'd expect to see float or double used in most cases.

[TonyRPH]

nyt said:

I would use Float. Decimal would probably work for your purposes but I'd expect to see float or double used in most cases.

Thanks for that.