Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools Display Modes
  #1  
Old 10-02-2012, 03:20 AM
singabaloo's Avatar
singabaloo singabaloo is offline
 
Join Date: May 2008
Posts: 54
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default SQL Query - format Date

I'm trying to run a MySQL query to list the members of a group and show the last activity date. The results gives me the date in a serial number like 1349148662

Here is my code. What do I need to add or change to format the query correctly ?

Code:
SELECT  `user`.`username` ,  `user`.`usergroupid` ,  `user`.`lastactivity` 
FROM  `user` 
WHERE (
`user`.`usergroupid` =  "7"
)
ORDER BY  `user`.`lastactivity` DESC 
LIMIT 0 , 30
Reply With Quote
  #2  
Old 10-02-2012, 04:08 AM
nerbert nerbert is offline
 
Join Date: May 2008
Posts: 784
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

What it's returning is the timestamp. You can use PHP to convert to the date format of your choice with date(format, timestamp)

http://www.w3schools.com/php/func_date_date.asp

I think there's a way to have MySQL do it but I forgot how it works
Reply With Quote
  #3  
Old 10-02-2012, 04:44 AM
singabaloo's Avatar
singabaloo singabaloo is offline
 
Join Date: May 2008
Posts: 54
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Thanks. I've found theMySql format but I can't seem to get it to display. I get no errors so I assume it's not massively wrong.

Code:
SELECT `user`.`username`, `user`.`usergroupid` , DATE_FORMAT(`user`.`lastactivity`, '%d/%m/%Y') as `date_string`
FROM `user`
WHERE (`user`.`usergroupid` ='7' OR `user`.`usergroupid` ='5')
ORDER BY `user`.`lastactivity` DESC
I want to try and keep it as MySQL code so I can add it to the queries.php and allow other admins to run the canned query.

--------------- Added [DATE]1349158474[/DATE] at [TIME]1349158474[/TIME] ---------------

OK, I got it working using the FROM_UNIXTIME function.

Code:
SELECT username, usergroupid , FROM_UNIXTIME(lastactivity) AS ladate
FROM user
WHERE (usergroupid ='7' OR usergroupid ='5')
ORDER BY ladate DESC
Reply With Quote
  #4  
Old 10-02-2012, 05:25 AM
nerbert nerbert is offline
 
Join Date: May 2008
Posts: 784
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I guess that isn't a timestamp, but is generated by the PHP time() function at the time of the update. What MySQL wants is a proper timestamp, which looks like this: 2008-11-11 12:45:34. I don't know how you would convert using MySQL
Reply With Quote
  #5  
Old 10-02-2012, 05:35 AM
singabaloo's Avatar
singabaloo singabaloo is offline
 
Join Date: May 2008
Posts: 54
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

As above, using the FROM_UNIXTIME I was able to reformat the lastactivity time correctly.

Now to tackle getting a search from two separate tables working.
Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 01:00 PM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.03699 seconds
  • Memory Usage 2,200KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (3)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (5)post_thanks_box
  • (5)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (5)post_thanks_postbit_info
  • (5)postbit
  • (5)postbit_onlinestatus
  • (5)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete