XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. Q and A 
  4.  / how to work with _priv_msgs:msg_time in sql
Register
1
kjs222
how to work with _priv_msgs:msg_time in sql

  • 2003/11/13 18:21

  • kjs222

  • Friend of XOOPS

  • Posts: 116

  • Since: 2003/3/1 1


(Copied from another forum)

Can anyone tell me the SQL statement I could use to select msg_time as an interpretable date from _priv_msgs?

so for instance, something like:

select functionName(msg_time) from _priv_msgs;

Thanks,
keith
2
ackbarr
Re: how to work with _priv_msgs:msg_time in sql

well I took a look at the MySQL documentation, and I didn't see a function that converts a Unix Timestamp into an actual date value. However you can retrieve the unix timestamp from the MySQL db and use the XOOPS function formatTimestamp() to format it any way you'd like:
//Code to retrieve priv_msgs from db omitted
//assume that the timestamp is in $msg_time

//To display a full date (according to your language settings)
$mytime = formatTimestamp($msg_time);

//To display a short date
$mytime = formatTimestamp($msg_time, 's');

//To display in a custom date format (uses php date() function)
$mytime = formatTimestamp($msg_time, 'Y-m-d H:i:s');


Additional Resources:
Mysql Date and Time Functions
PHP date() function
3
kjs222
Re: how to work with _priv_msgs:msg_time in sql

  • 2003/11/13 22:41

  • kjs222

  • Friend of XOOPS

  • Posts: 116

  • Since: 2003/3/1 1


Finally found the answer!!! Thanks for the guidance ackbarr:

Select FROM_UNIXTIME(msg_time);

Note that XOOPS does some extra stuff with it using its functions.. most of these are related to localization. See include/functions.php for details:

function xoops_getUserTimestamp($time, $timeoffset="")
{
    global $xoopsConfig, $xoopsUser;
    if ($timeoffset == '') {
        if ($xoopsUser) {
            $timeoffset = $xoopsUser->getVar("timezone_offset");
        } else {
            $timeoffset = $xoopsConfig['default_TZ'];
        }
    }
    $usertimestamp = intval($time) + (intval($timeoffset) - $xoopsConfig['server_TZ'])*3600;
    return $usertimestamp;
}
from: http://cvs.sourceforge.net/viewcvs.py/*checkout*/xoops/xoops2/include/functions.php?content-type=text%2Fplain&rev=1.64

Hope that helps others!

Keith

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Donat-O-Meter

Stats
Goal: $15.00
Due Date: Sep 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $15.00
Make donations with PayPal!