Difficulty handling returned timestamps from a SQL Query |
[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'] And part of ch_data_view: ------------------------- Code: <?php
[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 from my point of view, it's better to save timestamp into database and convert it by CI / PHP to display.
[eluser]Murodese[/eluser]
strtotime() should be able to convert a mysql timestamp into a correct unix timestamp.
[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.
[eluser]dmorin[/eluser]
Checkout http://www.bigroom.co.uk/blog/dates-in-php-and-mysql for some options on handling date/time in mysql.
[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.
[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.
[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?
[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. |
Welcome Guest, Not a member yet? Register Sign In |