Removing the seconds from the time_date

In mySQL the time_date displays 2019-09-24 14:29:33

I’d like it to display that without the ‘seconds’.

I saw this, but I’m not sure if it will be the fix:

SELECT DATE_FORMAT(date, '%Y-%m-%d %H:%i') ASformatted_dateFROMtable;

I look forward to any comments

what happened when you test it? ™

I entered this into Run mysql:

SELECT DATE_FORMAT(time_date, '%Y-%m-%d %H:%i') ASformatted_dateFROMp_videos;

a column of formatted dates appeared, but nothing changed in the table or in the list on html page.

Any additional guidance is appreciated

Which makes sense, because you only ran a SELECT - that only retrieves data from the database and in this case show it to you formatted. Remember this: a SELECT query will never change what is in the database, it will only display it. Never? No, never. Not even when? No. Just, never.

Well, the SELECT did not change your PHP code, nor did it change some global setting in the database as you’ve figured out, so that’s why nothing happened.

If you don’t want to show the seconds on your web page, change the query for your webpage, using DATE_FORMAT like you used above.

Thanks for your reply.
I believe you are saying try this on the web page?

So, I added DATE_FORMAT, etc. into the (working) web page query, without success:

$get_videos = $db->rawQuery("SELECT DISTINCT(v.id), v.*, upv.id_user as id_user, upv.user_id_uploaded = user_id_uploaded, upv.earned_amount as earned_amount, upv.time_date as DATE_FORMAT(time_date, '%Y-%m-%d %H:%i'), upv.id as id FROM p_videos AS upv LEFT JOIN " . T_VIDEOS . " AS v ON (upv.id_video = v.id) WHERE upv.time_date AND upv.user_id_uploaded = {$user->id} ORDER BY upv.time_date DESC");

any additional guidance is welcomed

this is wrong –

upv.time_date as DATE_FORMAT(time_date, '%Y-%m-%d %H:%i')

a column alias does not allow a function, it can be only an identifier name

also, DISTINCT is ~not~ a function – it applies to ~all columns~ in the SELECT clause… so there’s no point in wrapping the first of those columns in parentheses

Thanks for your reply.
I appreciate the info, however, I’m not sure what the solution might be.
Any additional guidance is appreciated.

I have not checked but think Mysql dates are stored as a very, very long integer or a float/real/double number which is ideal for storage, indexing, etc.

It is up to you how the number is converted and displayed.

this is really really basic stuff

change this –

upv.time_date as DATE_FORMAT(time_date, '%Y-%m-%d %H:%i')

to this –

DATE_FORMAT(upv.time_date, '%Y-%m-%d %H:%i') AS time_date
1 Like