Welcome to Geeklog, Anonymous Tuesday, December 10 2024 @ 12:24 am EST
Geeklog Forums
Format of date varchar in gl_forum_topic.date and gl_forum_topic.lastupdated
Status: offline
jcz
Forum User
Chatty
Registered: 01/29/02
Posts: 39
We have to provide a daily dump of forum posts (readable by non-geeks in Excel). So, I need to be able to get the date into something understandable, and I'm no programmer.
Can someone please tell me what these values represent? My searches on this site are not turning up what I'm looking for.
`id` mediumint(8) NOT NULL AUTO_INCREMENT,
`forum` int(3) NOT NULL DEFAULT '0',
...
`date` varchar(12) DEFAULT NULL,
`lastupdated` varchar(12) DEFAULT NULL,
...
BTW, I'd really like to get this info out with a cron'd mysql query to local text file, so hopefully it's not too cryptic.
Can someone please tell me what these values represent? My searches on this site are not turning up what I'm looking for.
Text Formatted Code
CREATE TABLE `gl_forum_topic` (`id` mediumint(8) NOT NULL AUTO_INCREMENT,
`forum` int(3) NOT NULL DEFAULT '0',
...
`date` varchar(12) DEFAULT NULL,
`lastupdated` varchar(12) DEFAULT NULL,
...
BTW, I'd really like to get this info out with a cron'd mysql query to local text file, so hopefully it's not too cryptic.
20
13
Quote
Status: offline
jcz
Forum User
Chatty
Registered: 01/29/02
Posts: 39
Perfect. Thanks!
SELECT
`gl_forum_forums`.`forum_name` AS `forum_name`,
`gl_forum_topic`.`name` AS `name`,
FROM_UNIXTIME(`gl_forum_topic`.`name`) AS `date`,
FROM_UNIXTIME(`gl_forum_topic`.`lastupdated`) AS `lastupdated`,
`gl_forum_topic`.`last_reply_rec` AS `last_reply_rec`,
`gl_forum_topic`.`subject` AS `subject`,
`gl_forum_topic`.`comment` AS `comment`,
`gl_forum_topic`.`replies` AS `replies`,
`gl_forum_topic`.`views` AS `views`,
`gl_forum_topic`.`id` AS `id`
FROM (`gl_forum_forums`
JOIN `gl_forum_topic`
ON ((`gl_forum_forums`.`forum_id` = `gl_forum_topic`.`forum`)))
ORDER BY `gl_forum_forums`.`forum_name`,`gl_forum_topic`.`id`
Text Formatted Code
CREATE ALGORITHM=UNDEFINED DEFINER=`me`@`localhost` SQL SECURITY DEFINER VIEW `v_forum_posts` AS SELECT
`gl_forum_forums`.`forum_name` AS `forum_name`,
`gl_forum_topic`.`name` AS `name`,
FROM_UNIXTIME(`gl_forum_topic`.`name`) AS `date`,
FROM_UNIXTIME(`gl_forum_topic`.`lastupdated`) AS `lastupdated`,
`gl_forum_topic`.`last_reply_rec` AS `last_reply_rec`,
`gl_forum_topic`.`subject` AS `subject`,
`gl_forum_topic`.`comment` AS `comment`,
`gl_forum_topic`.`replies` AS `replies`,
`gl_forum_topic`.`views` AS `views`,
`gl_forum_topic`.`id` AS `id`
FROM (`gl_forum_forums`
JOIN `gl_forum_topic`
ON ((`gl_forum_forums`.`forum_id` = `gl_forum_topic`.`forum`)))
ORDER BY `gl_forum_forums`.`forum_name`,`gl_forum_topic`.`id`
15
13
Quote
Status: offline
jcz
Forum User
Chatty
Registered: 01/29/02
Posts: 39
This view actually does what I want it to do.
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`localhost` SQL SECURITY DEFINER VIEW `v_forum_posts` AS
select
`a`.`forum_name` AS `forum_name`,
`b`.`id` AS `id`,
`b`.`name` AS `name`,
from_unixtime(`b`.`lastupdated`) AS `lastupdated`,
from_unixtime(`b`.`date`) AS `date`,
`b`.`last_reply_rec` AS `last_reply_rec`,
`b`.`subject` AS `subject`,
`b`.`comment` AS `comment`,
`b`.`replies` AS `replies`,
`b`.`views` AS `views`
from (`gl_forum_forums` `a`
join `gl_forum_topic` `b`)
where (`a`.`forum_id` = `b`.`forum`)
order by `a`.`forum_name`,`b`.`id`
Text Formatted Code
CREATE ALGORITHM=UNDEFINED DEFINER=`user`@`localhost` SQL SECURITY DEFINER VIEW `v_forum_posts` AS
select
`a`.`forum_name` AS `forum_name`,
`b`.`id` AS `id`,
`b`.`name` AS `name`,
from_unixtime(`b`.`lastupdated`) AS `lastupdated`,
from_unixtime(`b`.`date`) AS `date`,
`b`.`last_reply_rec` AS `last_reply_rec`,
`b`.`subject` AS `subject`,
`b`.`comment` AS `comment`,
`b`.`replies` AS `replies`,
`b`.`views` AS `views`
from (`gl_forum_forums` `a`
join `gl_forum_topic` `b`)
where (`a`.`forum_id` = `b`.`forum`)
order by `a`.`forum_name`,`b`.`id`
13
12
Quote
All times are EST. The time is now 12:24 am.
- Normal Topic
- Sticky Topic
- Locked Topic
- New Post
- Sticky Topic W/ New Post
- Locked Topic W/ New Post
- View Anonymous Posts
- Able to post
- Filtered HTML Allowed
- Censored Content