-1

I am using freeradius base billing system which is using mysql as its backend DB engine. Table structure is as follows,

enter image description here

Thousands of users account expiration is stored in VARCHAR column and I cannot change its column type because in this table some other information is stored in varchar as well. EXample: enter image description here

. Date is stored in below format (in VARCHAR column)

29 Jul 2018 12:00:00

30 Jul 2018 12:00:00

I need to change only 12:00:00 to 00:00:00 so that it will look alike

30 Jul 2018 00:00:00
3

Assuming the following table structure and table name:

MariaDB [freeradius]> SHOW CREATE TABLE users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) DEFAULT NULL,
  `attribute` varchar(64) DEFAULT NULL,
  `op` char(2) DEFAULT NULL,
  `value` varchar(253) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=latin1

And having this sample data:

MariaDB [freeradius]> SELECT * FROM users;
+------+----------+--------------------+------+----------------------+
| id   | username | attribute          | op   | value                |
+------+----------+--------------------+------+----------------------+
| 1980 | 11test   | Cleartext-Password | :=   | 12345                |
| 1981 | 11test   | Expiration         | :=   | 29 Jul 2018 12:00:00 |
| 1982 | 11test   | Simultaneous-Use   | :=   | 1                    |
| 2000 | 22test   | Expiration         | :=   | 30 Jul 2018 12:00:00 |
+------+----------+--------------------+------+----------------------+
4 rows in set (0.000 sec)

Assuming all data to be changed has the 'Expiration' attribute, and all has the 12:00:00 timestamp,

the following UPDATE command should work:

MariaDB [freeradius]> UPDATE users SET value = DATE_FORMAT(STR_TO_DATE(value, '%e %b %Y %H:%i:%S') - INTERVAL 12 HOUR, '%e %b %Y %H:%i:%S') WHERE attribute = 'Expiration';           
Query OK, 2 rows affected (0.001 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [freeradius]> SELECT * FROM users;
+------+----------+--------------------+------+----------------------+
| id   | username | attribute          | op   | value                |
+------+----------+--------------------+------+----------------------+
| 1980 | 11test   | Cleartext-Password | :=   | 12345                |
| 1981 | 11test   | Expiration         | :=   | 29 Jul 2018 00:00:00 |
| 1982 | 11test   | Simultaneous-Use   | :=   | 1                    |
| 2000 | 22test   | Expiration         | :=   | 30 Jul 2018 00:00:00 |
+------+----------+--------------------+------+----------------------+
4 rows in set (0.001 sec)

You can test it first with a read-only query:

SELECT DATE_FORMAT(STR_TO_DATE(value, '%e %b %Y %H:%i:%S') - INTERVAL 12 HOUR, '%e %b %Y %H:%i:%S')
FROM users
WHERE attribute = 'Expiration';

The exact update operation may change if data is not normalized, or depending on the exact date format, with is not 100% clear (e.g. are 1-digit days of the month starting with a 0?).

This answers your question, but I want to signal some red flags on your schema, which would be too important not to mention:

  • thank you for detailed explanation. & the cmd worked fine :) – iBBi Jul 2 '18 at 3:19
2

If I haven't missed something, a simple replace() should do it.

UPDATE elbat
       SET value = replace(value, '12:00:00', '00:00:00')
       WHERE attribute = 'Expiration';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.