1

I have created a table of events on MySQL like

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);

Now when I query the table for dt I am getting 2018-01-04 00:00:00. How can I convert it to format of Jan, 4 to be displayed?

halfer
  • 19,824
  • 17
  • 99
  • 186
Mona Coder
  • 6,212
  • 18
  • 66
  • 128
  • Possible duplicate of [Convert one date format into another in PHP](https://stackoverflow.com/questions/2167916/convert-one-date-format-into-another-in-php) –  Dec 18 '17 at 02:43

2 Answers2

2

You can use DATE_FORMAT() function

SELECT DATE_FORMAT(dt, "%b, %e") as dt FROM events

Additionally, you can use same function for INSERT statements

INSERT INTO events(`ts`, `dt`)
VALUES ('some timestamp', DATE_FORMAT('2018-01-15 00:00:00', '%b, %e'))
Goma
  • 2,018
  • 1
  • 10
  • 19
  • thanks a lot s Erwin. ANy idea how can I acive same process on insert? I mean if user inserted `Jan, 5` on client side how can I insert to database in standard database format of `2018-01-05 00:00:00` – Mona Coder Dec 18 '17 at 02:52
  • @MonaCoder this answer exactly gave you what you asked for. Now you are expanding the question? – ryantxr Dec 18 '17 at 02:56
  • @ryantxr, Doesn't hurt to add some helpful codes. MonaCoder, try to practice adding all possible problems to your question next time. See edited answer :) – Goma Dec 18 '17 at 03:15
  • Great, I really appreciate your time and effort sir! – Mona Coder Dec 18 '17 at 03:30
0
<?php
$val = "2018-01-04 00:00:00";
$time = date_create($val);
echo date_format($time, "M, j");

// output: Jan, 4
user3120048
  • 33
  • 7