Removing the seconds from the time_date

[ChrisjChrisj]

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') AS formatted_dateFROMtable;

I look forward to any comments

[r937]

@ChrisjChrisj

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

what happened when you test it? ™

[ChrisjChrisj]

I entered this into Run mysql:

SELECT DATE_FORMAT(time_date, '%Y-%m-%d %H:%i') AS formatted_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

[rpkamp]

@ChrisjChrisj

but nothing changed in the table

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.

@ChrisjChrisj

or in the list on html page

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.

[ChrisjChrisj]

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

[r937]

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

[ChrisjChrisj]

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

[John_Betong]

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.

[r937]

@ChrisjChrisj

I appreciate the info, however, I’m not sure what the solution might be.

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