如何使用sql中的时间戳列从表中获取过去30天的记录

Question

我想在SQL中使用时间戳列，而不是我的查询的日期列来获取过去30天的记录

SELECT sum(gasFeeInUSD) as total, STR_TO_DATE(created, '%Y-%m-%d') as t_date
FROM `transactions`
WHERE STR_TO_DATE(created, '%Y-%m-%d') >= CURRENT_DATE - INTERVAL 30 DAY AND CURDATE()
GROUP BY DATE(STR_TO_DATE(created, '%Y-%m-%d'))

这个查询可以很好地处理已创建的列，但我想将它用于时间戳列。

这是数据库结构

CREATE TABLE IF NOT EXISTS `transactions` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`transferType` varchar(256) DEFAULT NULL,
`from` varchar(256) DEFAULT NULL,
`to` varchar(256) DEFAULT NULL,
`sendFrom` varchar(256) DEFAULT NULL,
`sendTo` varchar(256) DEFAULT NULL,
`amount` float DEFAULT NULL,
`currency` varchar(256) DEFAULT NULL,
`id_` varchar(256) DEFAULT NULL,
`timestamp` varchar(256) DEFAULT NULL,
`othersNumioId` varchar(256) DEFAULT NULL,
`othersName` varchar(256) DEFAULT NULL,
`message` varchar(256) DEFAULT NULL,
`transactionHash` varchar(256) DEFAULT NULL,
`totalTokenAmountInUSD` varchar(256) DEFAULT NULL,
`gasFeeInUSD` varchar(256) DEFAULT NULL,
`filterId` varchar(256) DEFAULT NULL,
`status` varchar(256) DEFAULT NULL,
`walletNo` varchar(256) DEFAULT NULL,
`import_id` int(11) DEFAULT NULL,
`day` varchar(256) DEFAULT NULL,
`date` varchar(256) DEFAULT NULL,
`month` varchar(256) DEFAULT NULL,
`year` varchar(256) DEFAULT NULL,
`created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
COMMIT;
-- Dumping data for table `transactions`

INSERT INTO `transactions` (`id`, `transferType`, `from`, `to`, `sendFrom`, `sendTo`, `amount`, `currency`, `id_`, `timestamp`, `othersNumioId`, `othersName`, `message`, `transactionHash`, `totalTokenAmountInUSD`, `gasFeeInUSD`, `filterId`, `status`, `walletNo`, `import_id`, `day`, `date`, `month`, `year`, `created`, `modified`) VALUES
(1, 'Send', '0x11F83064t5C59eBCDE1842FE28EB0ee6E77cbD3E', '0xFEe0F95a9AD7918A8trtc8F526d7a5315060050', 'Standard', 'Standard', 0.002263, 'ETH', '609212edbbe53e0019c78e42', '1621743092.497', 'timios1', 'Tim Allard', 'Hey', '0x8434d39cbe7db1afd95arta718881bbd78ccdf8684173a523b7ecd0afe3926e', '5.20462844', '1.69', '_1xdnjc6ja', 'Success', 't', 359, 'sun', '23', 'may', '2021', '2021-05-23 20:12:40', '2021-05-23 20:12:40'),
(2, 'Send', '0x22D227caCd975gAF0E326471976A818ef00F97889', '0x8b429171e521F2c83d6r4dAfe402fdB17eF3b3397B', 'Standard', 'Standard', 0.01, 'ETH', '609acbacde0cad0012e9afa7', '1621732567.403', 'timios', 'Tim Allard', 'Test from android how long can I make this message before it messes up the app?', '0xd1da2ef9e6ce3974746a40f242e22749fc4d27a1ed4e59340446da7ccf37e4b0', '23.5907', '1.68', '_dmzg4yswb', 'Success', 't', 358, 'sun', '23', 'may', '2021', '2021-05-23 20:12:40', '2021-05-23 20:12:40'),
(3, 'Internal Transfer', 'Numio Wallet', 'Standard Wallet', 'Numio', 'Standard', 10, 'USDC', '60a92c9d11afcse1200b257', '1621700163.749', NULL, NULL, NULL, 'sync-tx:e2878a8c3fad61057bdsd3e658d8e63f7b826e0d1b4c1d43b9ed113194367749', '9.991', '0.017', '_y2tn0rzjy', 'Success', NULL, 355, 'sat', '22', 'may', '2021', '2021-05-23 20:12:40', '2021-05-23 20:12:40'),
(4, 'Internal Transfer', 'Standard Wallet', 'Numio Wallet', 'Standard', 'Numio', 0.1, 'ETH', '60a92c9d11afdsdc0001200b257', '1621700005.226', NULL, NULL, NULL, '0x4b5350ddac9d6a1364986e49aa96522dsd321e1c42a64f38a765078add239dbc9af', '234.379', '7.83', '_tudolue4d', 'Success', NULL, 354, 'sat', '22', 'may', '2021', '2021-05-23 20:12:40', '2021-05-23 20:12:40'),
(5, 'Internal Transfer', 'Standard Wallet', 'Numio Wallet', 'Standard', 'Numio', 100, 'USDC', '60a92c9d11afdc0001200b257', '1621699943.112', NULL, NULL, NULL, '0xae27a438390esdd11f936a49dsbc1bf58a44b53cc00b27d8c84cfe0cb1ce2479c3276', '99.91', '21.82', '_vzvcawog0', 'Success', NULL, 353, 'sat', '22', 'may', '2021', '2021-05-23 20:12:40', '2021-05-23 20:12:40');
COMMIT;

Answer 1

如果我猜测是正确的，varchar(256)timestamp以毫秒为单位存储Unix时间戳，您只需要在将其除以10000后进行转换，如下所示：

WHERE FROM_UNIXTIME(timestamp / 1000) >= CURRENT_DATE - INTERVAL 30 DAY

或者你可以转换等式的正确部分：

WHERE (timestamp / 1000) >= UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 30 DAY)

另外，我想你可能想把CURRENT_DATE切换到CURRENT_TIME

Answer 2

您的时间戳看起来像是自1970-01-01以来以毫秒为单位测量的Unix时间戳类型。

如果你想在时间戳前30天，只需使用算术：

where timestamp < (unix_timestamp() * 1000) - (24 * 60 * 60 * 1000)