How to convert timestamp to date in MySQL Select query?

Dung Do Tien Oct 04 2022 324

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!

Have 1 answer(s) found.
  • T

    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.

Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips

X Close