Convert timestamp to date in MySQL query
DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'
Convert Timestamp to MYSQL Date In Query usable in WHERE
One method is to use having
:
SELECT t.*,
DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y-%m-%d %H:%i:%s') AS date_formatted
FROM `table` t
HAVING date_formatted >= '20210801' ;
However, it is better to phrase this as:
SELECT t.*,
DATE_FORMAT(FROM_UNIXTIME(`timestamp`), '%Y-%m-%d %H:%i:%s') AS date_formatted
FROM `table` t
WHERE timestamp >= UNIX_TIMESTAMP('2021-08-01');
This optimizer take advantage of indexes and statistics on the timestamp
column.
How to convert timestamp to datetime in MySQL?
Use the FROM_UNIXTIME()
function in MySQL
Remember that if you are using a framework that stores it in milliseconds (for example Java's timestamp) you have to divide by 1000 to obtain the right Unix time in seconds.
Convert Unix timestamp into human readable date using MySQL
Use FROM_UNIXTIME()
:
SELECT
FROM_UNIXTIME(timestamp)
FROM
your_table;
See also: MySQL documentation on FROM_UNIXTIME()
.
How to convert timestamp to date MySQL
Use FROM_UNIXTIME(str,format).
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime
The second parameter will let you format the date string to your liking.
Formatting characters:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
Get only the date in timestamp in mysql
You can use date(t_stamp)
to get only the date part from a timestamp.
You can check the date() function in the docs
DATE(expr)
Extracts the date part of the date or datetime expression expr.
mysql> SELECT DATE('2003-12-31 01:02:03');
-> '2003-12-31'
How to convert timestamp to date only where timestamp is not null
Indeed, unix timestamp 0
does map to timestamp 1970-01-01 00:00:00
.
0
is not a valid date, so you can't have that in the column. An alternative would be to return null
instead:
case when timestamp > 0 then from_unixtime(timestamp) end as new_timestamp
How to convert MySQL TIMESTAMP to date time in PHP
To do this the OO (and most flexible) way use DateTime
class and use the static createFromFormat
method to instantiate a new DateTime
object:
$new_datetime = DateTime::createFromFormat ( "Y-m-d H:i:s", $row["timestamp"] );
Now you can use the $new_datetime
object to generate any string representation you'd like by calling the object's format
method:
echo $new_datetime->format('d/m/y, H:i:s');
To boot, you since you've a DateTime
object you can now also to any manner of transformation (like shifting timezones or adding days), comparison (greater or less than another DateTime
), and various time calculations (how many days/months/etc... between this and another DateTime
).
DateTime:http://php.net/manual/en/class.datetime.php
Learn it. Love it. Live it.
Related Topics
Pass Multiple Values in Single Parameter
Using Stored Procedure in Classical Asp .. Execute and Get Results
Entity Framework VS Linq to SQL VS Ado.Net With Stored Procedures
How to Find Duplicate Values in a Table in Oracle
Reset Auto Increment Counter in Postgres
How to Get Column Names from a Table in Oracle
SQL Joins VS SQL Subqueries (Performance)
Dynamically Create Columns Sql
"Case" Statement Within "Where" Clause in SQL Server 2008
SQL Query - Using Order by in Union
SQL Server Check Case-Sensitivity
SQL Server - in Clause With a Declared Variable
How to Get the Difference in Years from Two Different Dates
Parse Comma-Separated String to Make in List of Strings in the Where Clause
Oracle SQL - How to Retrieve Highest 5 Values of a Column
Exporting Data from SQL Server Express to CSV (Need Quoting and Escaping)