Welcome to Geeklog Thursday, September 23 2021 @ 01:53 am EDT

Geeklog Forums

Format of date varchar in gl_forum_topic.date and gl_forum_topic.lastupdated


Status: offline

jcz

Forum User
Chatty
Registered: 29/01/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.

PHP 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.
 Quote

Status: offline

Dirk

Site Admin
Admin
Registered: 12/01/02
Posts: 13073
Location:Stuttgart, Germany
It's a Unix timestamp. Try something like
PHP Formatted Code
SELECT FROM_UNIXTIME(date) AS mydate FROM gl_forum_topic;

bye, Dirk
 Quote

Status: offline

jcz

Forum User
Chatty
Registered: 29/01/02
Posts: 39
Perfect. Thanks!

PHP 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`

 Quote

Status: offline

jcz

Forum User
Chatty
Registered: 29/01/02
Posts: 39
Why are all the dates in forum_topic.date "1969 31st December 07:00:00 1970"?

 Quote

Status: offline

jcz

Forum User
Chatty
Registered: 29/01/02
Posts: 39
This view actually does what I want it to do.


PHP 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`
 
 Quote

All times are EDT. The time is now 01:53 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