Welcome Guest, Not a member yet? Register   Sign In
Difficulty handling returned timestamps from a SQL Query
#1

[eluser]Unknown[/eluser]
Hi,

I have a MySql table that has a DATETIME field. The application inserts data with GMT time stamps. I am having trouble retrieving handling the timestamp variable when its retrieved from the database. I wanted to use the "gm" function to return the timestamp in local time but it wont accept $row->timeStamp as a valid variable.

I guess I am not clear on how PHP handles $row->timestamp. Can someone enlighten me?

Thanks
Wayne


This is part of my controller function:
--------------------------------------
Code:
$sql= 'SELECT SensorData.timeStamp, SensorData.'.$data['channel']
      .' AS value FROM SensorData WHERE device_serial ="'.$data["serial"].'"';

$data['channel']= $this->db->query($sql);
$data['title']= 'Channel Data';
$this->load->view("ch_data_view",$data);

And part of ch_data_view:
-------------------------
Code:
<?php
setlocale(LC_TIME, 'en_US');

foreach ($channel->result() as $row):

$timeStamp= strftime("%b %d %Y %H:%M", $row->timeStamp);?>

<tr><td>&lt;?$timeStamp?&gt;</td><td>&lt;?=$row->value?&gt;</td></tr>

&lt;?php endforeach;?&gt;
#2

[eluser]Sumon[/eluser]
Not sure how difficult your desired output is. but let me explain in short what i do for date operations.
my mysql table have fields for date by example visit_date_time. it's data type is INT (11). now for a visitor i assign time() into visit_date_time.

when i need to display this information i pick it from database using a simple query (select visit_date_time from visitor_table). now convert it into day, month and year using easy functions. here are two functions what might help you.
Code:
function db_to_human($time)        //same as unix_to_human
{
    if(is_numeric($time) && $time>0)
    {
        return date("Y-m-d", $time);
    }
    else
    {
        return $time;
    }
}
function human_to_db($date)        //same as human_to_unix
{
    if(trim($date)=="") return FALSE;
    $date = explode("-",$date);
    return mktime(0,0,0,$date[1],$date[2],$date[0]);
}

from my point of view, it's better to save timestamp into database and convert it by CI / PHP to display.
#3

[eluser]Murodese[/eluser]
strtotime() should be able to convert a mysql timestamp into a correct unix timestamp.
#4

[eluser]Sumon[/eluser]
@Murodese: thanks for your reply. i feel, db_to_human($time) and human_to_db($date) functions are useful when user input like 2005-12-25. after submit it convert into unix timestamp by human_to_db($date) and save it into database as integer. later on, fetch integer data and convert into 2005-12-25 using db_to_human($time) function.
#5

[eluser]dmorin[/eluser]
Checkout http://www.bigroom.co.uk/blog/dates-in-php-and-mysql for some options on handling date/time in mysql.
#6

[eluser]Colin Williams[/eluser]
Not sure what the key argument is for not storing Unix-style timestamps, so I wouldn't even bother with any other format.
#7

[eluser]dmorin[/eluser]
As long as you're not using any MySQL built in functions to manipulate the dates, then unix-style is certainly the best. Unfortunately, not everyone is starting with a new database/schema, so being able to handle both situations may be required depending on the app.
#8

[eluser]Sumon[/eluser]
@colin: lets consider an example and discuss the best way using CI.
in a form there are one input date field using javascript calendar. when user select any date from calendar text box assigned the value. say it's 2008-11-19.
now how to store this value into database and later on display as 2008-11-19
another scenario: the time of registration also store in another field of database table. and later on display as date of your registration was: 2008-11-19

what's the best way to handle these two situations?
#9

[eluser]Pascal Kriete[/eluser]
I use timestamps almost exclusively.
All it takes is for the client to say he wants november instead of 11.

I'm not colin, but I will take a shot at those examples anyways. strtotime on insert for the first one, and date when displaying both of them. If anything changes, you know exactly where to go - and it's not a query.




Theme © iAndrew 2016 - Forum software by © MyBB