mysqldump of table with where condition for all data older than a certain epochtime value

Question

I have problems to automate by database backup by powershell. I want to export every day all records in a special table which are older (or better greater than >=) than a certain defined value for a Epoch Time.

My table looks like this structure:

mysql> show columns from generalhistory;
+-----------+--------------+------+-----+-------------------+----------------+
| Field     | Type         | Null | Key | Default           | Extra          |
+-----------+--------------+------+-----+-------------------+----------------+
| ID        | int(11)      | NO   | PRI | NULL              | auto_increment |
| DPName    | varchar(100) | NO   |     | NULL              |                |
| Value     | varchar(100) | NO   |     | NULL              |                |
| Timestamp | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| Manager   | varchar(100) | NO   |     | NULL              |                |
| EpochTime | bigint(20)   | NO   |     | NULL              |                |
+-----------+--------------+------+-----+-------------------+----------------+

here are my command which i try to execute in a powershell windows but there will nothing be exported from the data.

PS D:\xampp\mysql\bin> .\mysqldump.exe --no-create-db --no-create-info --skip-triggers -u root -pmypassword homescada generalhistory --where="EpochTime > '1470268800975'"

I also tried this solution but also without success.

PS D:\xampp\mysql\bin> .\mysqldump.exe --no-create-db --no-create-info --skip-triggers -u root -pmypassword homescada generalhistory --where="'Timestamp' BETWEEN '2016-06-11 09:26:01' AND '2016-08-05 09:26:01'"

do you knwo what i do wrong?#

many thanks

dieter

Possible duplicate of [PHP regular backup of mysql data](http://stackoverflow.com/questions/38916163/php-regular-backup-of-mysql-data) — e4c5, Aug 15 '16 at 00:52

score 1 · Answer 1 · answered Aug 07 '16 at 09:57

I tried this sql query and it works.

SELECT * FROM generalhistory
WHERE `Timestamp` BETWEEN '2016-06-11 09:26:01' AND '2016-08-05 09:26:01'

ID  DPName  Value   Timestamp   Manager EpochTime
2400    Buero.Sensor.Temp   28.60   "2016-06-11 09:26:01"   Event   1465629961000
2401    Buero.Sensor.Hum    46.40   "2016-06-11 09:26:01"   Event   1465629961000

I have really no idea, why i get no data during a dump :-(

Jocelyn · Answer 2 · 2016-08-06T21:04:29.470

MySQL table fields are usually enclosed in backticks, not single quotes. Try this command (on a single line):

PS D:\xampp\mysql\bin> .\mysqldump.exe --no-create-db --no-create-info --skip-triggers -u
root -pmypassword homescada generalhistory --where="`Timestamp`
BETWEEN '2016-06-11 09:26:01' AND '2016-08-05 09:26:01'"

The syntax is correct since you get a MySQL dump. You probably get no data because you WHERE clause is too restrictive. Connect to mysql and run the following query in homescada database:

SELECT * FROM generalhistory
WHERE `Timestamp` BETWEEN '2016-06-11 09:26:01' AND '2016-08-05 09:26:01'

Does it really return data?

The syntax is explained here: mysqldump with where option

@DSchneider I added more information to my answer, can you check again? — Jocelyn, Aug 06 '16 at 21:05

mysqldump of table with where condition for all data older than a certain epochtime value

2 Answers2