Convert Timestamp to Date in MySQL Query

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



Leave a reply



Submit