How to convert timestamp to date in MySQL Select query?

Dung Do Tien Oct 04 2022 168

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.

Related Q&A May You Like

  1. Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF.
  2. MySql throw no database selected, select the default DB to be used by double-clicking its name
  3. How to generate whole sql database script in MySql Workbench
  4. Error querySrv ENODATA _mongodb._tcp.456d in MongoDB Compass
  5. What is best way to convert datetime to date in Sql Server?
  6. How to compare date without time with now() in PostgreSQL?
  7. MySQL Error Code: 1175. safe update mode update a table without a WHERE
  8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements in MySQL
  9. ERROR: column must appear in the GROUP BY clause or be used in an aggregate function in PostgreSQL
  10. Calculating distance between two positions(lat, long) in SQL Server
  11. MySQL Error: 'Access denied for user 'root'@'localhost'
  12. Error: /opt/lampp/bin/mysql.server: 264: kill: No such process
  13. How to get the total of current connection in PostgreSQL?
  14. How to backup and restore MSSQL Server database in Ubuntu
  15. ERROR: OFFSET must not be negative in PostgreSQL
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.

Most Quizs Popular

Start Test

Javascript - Functions
Start Test

Asp.Net Core - Exam test 01
Start Test

Asp.Net Core - Exam test 02
Start Test

C# - Level Beginner

Popular Tips

This site uses cookies so that we can remember you and understand how you interact with our website. This allows us to improve and customize your browsing experience. See our Cookies Policy.
X Close