55 lines
2.0 KiB
SQL
55 lines
2.0 KiB
SQL
-- 将时间字段从TIMESTAMP改为INT（存储Unix时间戳秒数）
|
||
-- 此脚本会将现有的datetime/timestamp数据转换为Unix时间戳（秒）
|
||
|
||
USE `auto_trade_sys`;
|
||
|
||
-- 步骤1：添加新的INT类型字段（临时字段）
|
||
ALTER TABLE `trades`
|
||
ADD COLUMN `entry_time_ts` INT UNSIGNED NULL COMMENT '入场时间（Unix时间戳秒数）' AFTER `entry_time`,
|
||
ADD COLUMN `exit_time_ts` INT UNSIGNED NULL COMMENT '平仓时间（Unix时间戳秒数）' AFTER `exit_time`,
|
||
ADD COLUMN `created_at_ts` INT UNSIGNED NULL COMMENT '创建时间（Unix时间戳秒数）' AFTER `created_at`;
|
||
|
||
-- 步骤2：将现有数据转换为Unix时间戳（秒）
|
||
-- 注意：假设现有数据是北京时间（UTC+8），转换为UTC时间戳
|
||
UPDATE `trades`
|
||
SET `entry_time_ts` = UNIX_TIMESTAMP(`entry_time`)
|
||
WHERE `entry_time` IS NOT NULL;
|
||
|
||
UPDATE `trades`
|
||
SET `exit_time_ts` = UNIX_TIMESTAMP(`exit_time`)
|
||
WHERE `exit_time` IS NOT NULL;
|
||
|
||
UPDATE `trades`
|
||
SET `created_at_ts` = UNIX_TIMESTAMP(`created_at`)
|
||
WHERE `created_at` IS NOT NULL;
|
||
|
||
-- 步骤3：删除旧的时间字段
|
||
ALTER TABLE `trades`
|
||
DROP COLUMN `entry_time`,
|
||
DROP COLUMN `exit_time`,
|
||
DROP COLUMN `created_at`;
|
||
|
||
-- 步骤4：重命名新字段
|
||
ALTER TABLE `trades`
|
||
CHANGE COLUMN `entry_time_ts` `entry_time` INT UNSIGNED NOT NULL COMMENT '入场时间（Unix时间戳秒数）',
|
||
CHANGE COLUMN `exit_time_ts` `exit_time` INT UNSIGNED NULL COMMENT '平仓时间（Unix时间戳秒数）',
|
||
CHANGE COLUMN `created_at_ts` `created_at` INT UNSIGNED NOT NULL DEFAULT (UNIX_TIMESTAMP()) COMMENT '创建时间（Unix时间戳秒数）';
|
||
|
||
-- 步骤5：更新索引
|
||
DROP INDEX IF EXISTS `idx_entry_time` ON `trades`;
|
||
CREATE INDEX `idx_entry_time` ON `trades` (`entry_time`);
|
||
|
||
-- 验证：查看转换后的数据
|
||
SELECT
|
||
id,
|
||
symbol,
|
||
entry_time,
|
||
FROM_UNIXTIME(entry_time) as entry_time_display,
|
||
exit_time,
|
||
FROM_UNIXTIME(exit_time) as exit_time_display,
|
||
created_at,
|
||
FROM_UNIXTIME(created_at) as created_at_display
|
||
FROM `trades`
|
||
ORDER BY id DESC
|
||
LIMIT 10;
|