Convert timestamp to date in MySQL query

Question

I want to convert a timestamp in MySQL to a date  I would like to format the user registration field into the text file as a yyyy-mm-dd  Here is my SQL   sql   requestSQL  quot SELECT user email                      info name                      FROM UNIXTIME user registration                      info news                    FROM user  info                     WHERE user id   info id  quot    quot export members quot     I also tried the date conversion with  DATE FORMAT user registration    d  m  Y   DATE user registration   I echo the result before to write the text file and I get    email1 name1 DATE FORMAT user registration    d  m  Y   news1 email2 name2 news2  How can I convert that field to date

User · Answer

Convert timestamp to date in MYSQL  Make the table with an integer timestamp   mysql gt  create table foo id INT  mytimestamp INT 11    Query OK  0 rows affected  0 02 sec    Insert some values  mysql gt  insert into foo values 1  1381262848   Query OK  1 row affected  0 01 sec    Take a look  mysql gt  select   from foo   ------ -------------    id     mytimestamp    ------ -------------       1    1381262848    ------ -------------  1 row in set  0 00 sec    Convert the number to a timestamp   mysql gt  select id  from unixtime mytimestamp  from foo   ------ ----------------------------    id     from unixtime mytimestamp     ------ ----------------------------       1   2013-10-08 16 07 28           ------ ----------------------------  1 row in set  0 00 sec    Convert it into a readable format   mysql gt  select id  from unixtime mytimestamp    Y  D  M  H  i  s   from foo   ------ -------------------------------------------------    id     from unixtime mytimestamp    Y  D  M  H  i  s      ------ -------------------------------------------------       1   2013 8th October 04 07 28                          ------ -------------------------------------------------  1 row in set  0 00 sec

User · Answer

To just get a date you can cast it  cast user registration as date    and to get a specific format use date format  date format registration    Y- m- d     SQLFiddle demo

User · Answer

I did it with the  date  function as described in here      SELECT count    as the-counts  date timestamp   as the-timestamps FROM  user data  WHERE 1 group BY the-timestamps

User · Answer

You should convert timestamp to date   select FROM UNIXTIME user registration    Y- m- d  H  i  s   AS  date formatted    FROM UNIXTIME

User · Answer

you can try this The date is of timestamp type which has the following format     YYYY-MM-DD HH MM SS    or    2008-10-05 21 34 02      res   mysql query  quot SELECT date FROM times  quot    while    row   mysql fetch array  res         echo  row  date      quot   quot      The PHP strtotime function parses the MySQL timestamp into a Unix timestamp which can be utilized for further parsing or formatting in the PHP date function  Here are some other sample date output formats that may be of practical use  echo date  quot F j  Y g i a quot   strtotime  row  quot date quot                          October 5  2008 9 34 pm echo date  quot m d y quot   strtotime  row  quot date quot                                 10 05 08 echo date  quot j  n  Y quot   strtotime  row  quot date quot                               5  10  2008 echo date  quot Ymd quot   strtotime  row  quot date quot                                   20081005 echo date   i t  i s  t h e jS  d a y    strtotime  row  quot date quot           It is the 5th day  echo date  quot D M j G i s T Y quot   strtotime  row  quot date quot                       Sun Oct 5 21 34 02 PST 2008

User · Answer

FROM UNIXTIME unix timestamp   format   is all you need  FROM UNIXTIME user registration    Y- m- d   AS  date formatted    FROM UNIXTIME gets a number value and transforms it to a DATE object  or if given a format string  it returns it as a string   The older solution was to get the initial date object and format it with a second function DATE FORMAT    but this is no longer necessary

User · Answer

Try    SELECT strftime   Y- d- m   col name   unixepoch   AS col name   It will format timestamp in milliseconds to yyyy-mm-dd string

User · Answer

If the registration field is indeed of type TIMESTAMP you should be able to just do    sql    SELECT user email              info name              DATE user registration               info news       FROM user              info       WHERE user id   info id      and the registration should be showing as yyyy-mm-dd

User · Answer

Just use mysql s DATE function   mysql gt  select DATE mytimestamp  from foo

User · Answer

If you are getting the query in your output you need to show us the code that actually echos the result  Can you post the code that calls requeteSQL   For example  if you have used single quotes in php  it will print the variable name  not the value  echo  foo is  foo      foo is  foo   This sounds exactly like your problem and I am positive this is the cause   Also  try removing the   symbol to see if that helps by giving you more infromation   so that    SQL result    mysql query  SQL requete      run the query   becomes      SQL result   mysql query  SQL requete      run the query   This will stop any error suppression if the query is throwing an error

User · Answer

DATE FORMAT FROM UNIXTIME  user registration      e  b  Y   AS  date formatted

[mysql] Convert timestamp to date in MySQL query

Convert timestamp to date in MYSQL

Examples related to mysql

Examples related to sql

Examples related to date-formatting