asked on

mysql query. DATE_FORMAT

something i think should be working is not working for some reason....

date_format is not working like i expect it should.

THE QUERY:
SELECT
user_id,
user_created,
DATE_FORMAT('user_created', '%W %M %Y'),
user_updated,
DATE_FORMAT('user_updated', '%W %M %Y')
FROM jamroom_user

RETURNS:
user_id       user_created       DATE_FORMAT('user_created', '%W %M %Y')       user_updated       DATE_FORMAT('user_updated', '%W %M %Y')
0       1208885205       NULL       1218520017       NULL
1       1209195760       NULL       1217331051       NULL
2       1209196471       NULL       1218265035       NULL
3       1209810209       NULL       1214942818       NULL
4       1210155879       NULL       1218361991       NULL
5       1210206383       NULL       1214941640       NULL
6       1210215335       NULL       1212738378       NULL
7       1210223884       NULL       1212533688       NULL

I though it was the date_format function not working, but
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

returns:
DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')
Sunday October 2009

theGhost_k8

DATE_FORMAT('user_created', '%W %M %Y'),
Remove SINGLE QUOTES >> DATE_FORMAT(user_created, '%W %M %Y'),

Guy Hengel [angelIII / a3]

try:

SELECT 
user_id,
user_created,
DATE_FORMAT(`user_created`, '%W %M %Y'),
user_updated,
DATE_FORMAT(`user_updated`, '%W %M %Y')
FROM jamroom_user

Open in new window

ussher

ASKER

Nope. Neither of those worked. (i did try before asking the question) and also checked here (http://www.epochconverter.com/) to see that those numbers were in fact timestamps

SELECT
user_id,
user_created,
DATE_FORMAT(user_created, '%W %M %Y'),
user_updated,
DATE_FORMAT(user_updated, '%W %M %Y')
FROM jamroom_user

RESULT:
user_id       user_created       DATE_FORMAT(user_created, '%W %M %Y')       user_updated       DATE_FORMAT(user_updated, '%W %M %Y')
0       1208885205       NULL       1218522608       NULL
1       1209195760       NULL       1217331051       NULL
2       1209196471       NULL       1218265035       NULL

AND Angellll's code:
SELECT
user_id,
user_created,
DATE_FORMAT(`user_created`, '%W %M %Y'),
user_updated,
DATE_FORMAT(`user_updated`, '%W %M %Y')
FROM jamroom_user

SAME DEAL:
user_id       user_created       DATE_FORMAT(`user_created`, '%W %M %Y')       user_updated       DATE_FORMAT(`user_updated`, '%W %M %Y')
0       1208885205       NULL       1218522608       NULL
1       1209195760       NULL       1217331051       NULL
2       1209196471       NULL       1218265035       NULL
3       1209810209       NULL       1214942818       NULL

theGhost_k8

do convert epoc date to normal
Use: FROM_UNIXTIME(FIELD_NAME);

ASKER CERTIFIED SOLUTION

theGhost_k8

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ussher

ASKER

SELECT
user_id,
user_created,
FROM_UNIXTIME(user_created),
user_updated,
FROM_UNIXTIME(`user_updated`)
FROM jamroom_user

RETURNS:
user_id       user_created       FROM_UNIXTIME(user_created)       user_updated       FROM_UNIXTIME(`user_updated`)
0       1208885205       2008-04-22 10:26:45       1218522608       2008-08-11 23:30:08
1       1209195760       2008-04-26 00:42:40       1217331051       2008-07-29 04:30:51
2       1209196471       2008-04-26 00:54:31       1218265035       2008-08-08 23:57:15

theGhost_k8

please check you query, u missed out DATE_FORMAT part!!

ussher

ASKER

YIP, That did it!

user_id       user_created       DATE_FORMAT(FROM_UNIXTIME(user_created), '%W %M %Y')       user_updated       DATE_FORMAT(FROM_UNIXTIME(user_updated), '%W %M %Y')
0       1208885205       Tuesday April 2008       1218522608       Monday August 2008
1       1209195760       Saturday April 2008       1217331051       Tuesday July 2008
2       1209196471       Saturday April 2008       1218265035       Friday August 2008

Cheers.

Im sure ive done it before without that from_unixtime before.

Thanks for the help

ussher

ASKER

problem totally solved. although i wonder why this problem even happend. server setup.... dont know. but i have a working solution now. Thanks.