Member Avatar for devilhacker

Hi,
I have a table with a timedate collumn. I want to get the text of the article and its creation date, than to output them on my page.
How can I just get only the date value from a query or php script manupulation?

  • 3 Contributors
  • 4 Replies
  • 280 Views
  • 33 Minutes Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

Short answer for : either format the date in the query (so PHP receives a ready-made date field) or keep the full datetime and format it in PHP with DateTime for locale/control. pointed toward doing it in SQL; highlighted the timestamp-vs-datetime case and formatting in PHP — both approaches are valid depending on whether you need indexing/filtering or per-user formatting.

Example: produce a clean date column from MySQL using a format function, then read that alias in PHP.

SELECT add_date,
       DATE_FORMAT(add_date, '%Y-%m-%d') AS creation_date
FROM scms_latest_news;

In PHP you can then use the alias (for example $row['creation_date']) directly for output or further processing.

If you prefer handling formatting in PHP (better for localization or complex rules), use the DateTime API rather than the old date() helper:

$dt = new DateTime($row['add_date']);
$onlyDate = $dt->format('Y-m-d');

If your column stores a UNIX timestamp integer, create the DateTime from that timestamp (or convert it server-side first).

Two practical cautions:

  • For WHERE clauses, avoid wrapping the datetime column in a function (that disables index use). Instead filter with a range, e.g. add_date >= 'YYYY-MM-DD 00:00:00' AND add_date < 'YYYY-MM-DD 00:00:00' for the next day.
  • Note the storage type: TIMESTAMP undergoes timezone conversion; DATETIME does not. A common pattern is to store UTC in the database and convert/format in the application layer.

Alias the formatted date with a clear name (e.g. creation_date) to avoid reserved-word collisions and then use that alias in PHP for clean, maintainable output.

Recommended Answers

All 4 Replies

Member Avatar for pritaeas 
SELECT DATE(datetimecolumn) AS `date` FROM `table`
Member Avatar for blocblue

Misread your question. I'll leave my answer here for those who are looking for the same, but with a timestamp column

You can either select the timestamp from MySQL returning it in the format you desire - see FROM_UNIXTIME

SELECT FROM_UNIXTIME(`timestamp`, '%Y %D %M') AS `date FROM `table`;
-- 2007 30th November

Or, you can convert the timestamp into a date using PHP - see date

echo date('Y jS M', $timestamp);
// 2007 30th November
Edited by blocblue
Member Avatar for devilhacker

At first thank you for your help.
Maybe I was not clear in my description.
I am using this query: SELECT * FROM scms_latest_news;
The table scms_latest_news has 3 collunms id, content, add_date

$query = "SELECT * FROM scms_latest_news;";

I want to output the content is : $row['content'] . "Creation date:" . $row['???????']

Member Avatar for pritaeas 
SELECT *,DATE(add_date) AS `date` FROM `scms_latest_news`

Then you can use:

$row['date']
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.