-1

I have a table with a date column that is formatted as a DATETIME(3). I would like to convert it to a BIGINT(13) that stores that datetime as the milliseconds from unix epoch.

How can I modify the column definition and convert all of the existing values in the table without losing any of the data?

I know you can convert the datetime to milliseconds using taking into account the timezone:

SELECT CAST(
  UNIX_TIMESTAMP(
    CONVERT_TZ('2019-04-22 00:37:47.843', '+00:00', 'SYSTEM')
  ) * 1000 AS INT
);

Which returns: 1555893467843

The table is created as such:

CREATE TABLE `usages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `datetime` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
);

I would like it to look like this:

CREATE TABLE `usages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `unix` bigint(13) NOT NULL,
  PRIMARY KEY (`id`),
);
Pearce
  • 3
  • 1
  • Why do you want to ditch a perfectly good `DATETIME` for a `BIGINT`? – ceejayoz Apr 22 '19 at 21:16
  • I want to query the the table really quickly (2 queries per second) and it is faster to do comparisons on an int then a date. – Pearce Apr 22 '19 at 21:50
  • 1
    That sounds like a failure to index the column. I don't see an index in your table definition; chances are simply adding one would speed things up immensely. 2/second on a properly indexed table shouldn't tax even a wimpy server. – ceejayoz Apr 22 '19 at 21:52
  • I didn't include my indexed columns because they weren't relevant to my question. Im just trying to make it faster. I know that it works well as a DATETIME. – Pearce Apr 23 '19 at 22:02
  • If your question is about speed, the indexes are absolutely relevant. – ceejayoz Apr 23 '19 at 22:12

1 Answers1

1

Are you using MySQL 5.6 or higher? If so, before converting you table I would encourage you to consider simply changing your datetime column definition to datetime(6) which will natively support storing milliseconds. More discussion on this here: Timestamp with a millisecond precision: How to save them in MySQL

If you still want to alter your table you can do it as follows:

1) Rename your table:

RENAME TABLE `usages` TO `usages_original`;

2) Create usages as desired:

CREATE TABLE `usages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(36) NOT NULL,
  `unix` bigint(13) NOT NULL,
  PRIMARY KEY (`id`),
);

3) Insert your modified data:

INSERT INTO `usages`
SELECT `id`, `uuid`,
    UNIX_TIMESTAMP(CONVERT_TZ(`datetime`, '+00:00', 'SYSTEM')) * 1000
FROM `usages_original`

4) Optionally remove the renamed table: DROP usages_original

Side note: I don't think CAST() is necessary here but if you really want to cast as an integer, it should be instead be CAST expr AS UNSIGNED or you'll get an error.

But those new buttons though..
  • 21,377
  • 10
  • 81
  • 108