vamsiampolu vamsiampolu - 4 months ago 8
MySQL Question

Different values for datetime, date and time fields from timestamp in MySQL

I am trying to convert a

TIMESTAMP
to the
DATETIME
,
DATE
and
TIME
MySQL types using the
FROM_UNIXTIME
field. I noticed that the values for
TIME
and
DATETIME
fields have different time information on them.

Instead of the insert statement to the table, I am replacing it with a simple select statement that prints the values:

select FROM_UNIXTIME('1468561341') as timestamp_datetime,FROM_UNIXTIME('1468561341','%d/%m/%y') as timestamp_date, FROM_UNIXTIME('1468561341','%h:%m:%s %p') as timestamp_time


The results look like this:


  • timestamp_datetime:
    2016-07-15 05:42:21.000000

  • timestamp_date
    15/07/16

  • timestamp_time
    05:07:21 AM



How can I get consistent time value in both these columns?

Answer

the issue is with letter m

it should be i

mysql> select FROM_UNIXTIME(1468561341) as timestamp_datetime,FROM_UNIXTIME(1468561341,'%d/%m/%y') as timestamp_date, FROM_UNIXTIME(1468561341,'%h:%i:%s %p') as timestamp_time;
+---------------------+----------------+----------------+
| timestamp_datetime  | timestamp_date | timestamp_time |
+---------------------+----------------+----------------+
| 2016-07-15 05:42:21 | 15/07/16       | 05:42:21 AM    |
+---------------------+----------------+----------------+
1 row in set (0.05 sec)