How to use function with Table

Question

I created a table in which i used timestamp column which tells the record is updated on that time and date.

create table age_info (age tinyint not null,created_on timestamp not null );

But i want to change display like this.
select date_format(Now() ,'%W, %e %M %Y @ %r');

Tuesday, 31 July 2015 @ 02:32:16 PM

But don't know how to do it. When i insert values

insert into age_info(age) values (19);

in the table it show like this.

age      created_on
 19     2015-07-31 18:55:01

I Don't know how to use this function date_format with timestamp column to show like that format.

Possibly this - http://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query ? — Giles, Jul 07 '15 at 09:50

score 3 · Accepted Answer · edited Jul 07 '15 at 16:58

3

I think this will help you to solve the answer

SELECT DATE_FORMAT(`created_on` , '%W, %e %b %Y @ %r') FROM `age_info`;

To insert,

 INSERT INTO `age_info` (`age`) select DATE_FORMAT(now() , '%W %e, %b %Y @ %r')created_on from age_info

Note that, You need to change the table field to varchar.

edited Jul 07 '15 at 16:58

Ven Nilson

969
4
16
42

answered Jul 07 '15 at 09:55

Kiran LM

1,359
1
16
29

@Kiran LM why you write WHERE 1 at query and what it's mean – Ven Nilson Jul 07 '15 at 10:02
no, that's just an example, to insert try this : `INSERT INTO `age_info` (`created_on`) select DATE_FORMAT(now() , '%W %e, %b %Y @ %r') as created_on from age_info` – Kiran LM Jul 07 '15 at 10:04
@Kiran LM does date_format function used for only select statement – Ven Nilson Jul 07 '15 at 10:07
Yes, It used to display datetime format so mostly used in selects, – Kiran LM Jul 07 '15 at 10:10
@Kiran LM does there any way we can save record in that format – Ven Nilson Jul 07 '15 at 10:18
check out the edit in the answer, there is the insert query. – Kiran LM Jul 07 '15 at 10:22

score 0 · Answer 2 · answered Jul 07 '15 at 09:50

0

You can keep the format you want by changing your column definition to a text column. Is that what you really want? You'll lose all the benefits a timestamp column offers (easy date and time calculations, etc).

answered Jul 07 '15 at 09:50

MeanGreen

3,098
5
37
63

score 0 · Answer 3 · answered Jul 07 '15 at 09:54

0

You can't change the datetime column type to store the data as you want, it changes the meaning of the datetime and no advantage of it, although you can use it to select the format whatever you want using date_format() function.

answered Jul 07 '15 at 09:54

Aman Aggarwal

17,619
9
53
81

How to use function with Table

3 Answers3