How to convert timestamp to date in MySQL Select query?
Hi everyone, I am working with My Sql version 8.0 and have a problem need to help.
I have created a table, like this:
CREATE TABLE `notifyhistory` (
`NotifyHistoryId` int NOT NULL AUTO_INCREMENT,
`UserId` int DEFAULT NULL,
`PushDate` date DEFAULT NULL,
`Status` bit(1) DEFAULT NULL,
`Message` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
`Title` varchar(100) DEFAULT NULL,
`Link` varchar(150) DEFAULT NULL,
PRIMARY KEY (`NotifyHistoryId`),
UNIQUE KEY `NotifyHistoryId_UNIQUE` (`NotifyHistoryId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Data example:
INSERT INTO `notifyhistory`(`NotifyHistoryId`,`UserId`,`PushDate`,`Status`,`Message`,`Title`,`Link`)
VALUES (100, 2, "2015-07-30", 0, null, "Send notify","");
Please focus on PushDate
column, it has datatype is date
. Now I want to select all record have PushDate
value = 1438269976.
1438269976
is timestamp value and it is input paramter and I cannot change it, when you parse it to date it will return "2015-30-07".
How can I convert timestame to date before compare them.
So how to convert timestamp to date in MySQL query?
Thanks in advance!
- T1
Trần quốc cường Oct 04 2022
You can use FROM_UNIXTIME() method it will help you convert from Timestapm to Datetime. For example:
Select FROM_UNIXTIME(1438269973) AS 'date_formatted' #Output: 2015-07-30 15:26:13
You need compare with date not datetime datatype, so you need one step to cast it. You can use
DATE_FORMAT()
method to do, here is full demo for you:select * from googlenotification.notifyhistory where PushDate = DATE_FORMAT(FROM_UNIXTIME(1438269973), '%Y-%m-%d')
Hope this answer will helful for you.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.