from_unixtime() out of control

[ad_ke]

Hi,

Is there a trick to get FROM_UNIXTIME to ignore the server's local time? I work in the western US and import unix timestamps from a data logger into a mysql database like this:

$timestamp = gmmktime($hours + 8, $minutes, $seconds, $month, $day, $year, $isDaylight); //where $isDaylight = FALSE.

This stores the correct timestamp in the database. The problem is trying to return a human readable time in MySQL. When I use CONVERT_TZ( FROM_UNIXTIME( t1.timestamp -28800 ) , 'US/Pacific', 'UTC' ) AS timestamp_1, I return a good timestamp, that is, the spring forward DST is ignored. However, the "fall back" in the fall duplicates the 8:00am hour each year. When I remove the CONVERT_TZ(), the "fall back" time is correct, but the the "spring forward" time is not.

Does anyone know of a way to "take control" of the FROM_UNIXTIME() so that it doesn't change the timestamps of the data stored in the database when queried?
Thanks in advance,
AK

[oesxyl]

from:

http://www.php.net/manual/en/function.gmmktime.php


Identical to mktime() except the passed parameters represents a GMT date. gmmktime() internally uses mktime() so only times valid in derived local time can be used.

Like mktime(), arguments may be left out in order from right to left, with any omitted arguments being set to the current corresponding GMT value.

for short if you use gmt anywhere, server and local, you don't need any conversion except the place where you want to display this and there you use the locale settings.

regards

[CFMaBiSmAd]

Is there a trick to get FROM_UNIXTIME to ignore the server's local time?...
Does anyone know of a way to "take control" of the FROM_UNIXTIME() so that it doesn't change the timestamps of the data stored in the database when queried?

Yes. Take your yyyy-mm-dd hh:mm:ss information that you are currently placing into the gmmktime() function and store it in a DATETIME field. It will always retain that value because you will no longer be passing it through any conversions. You can extract (using the mysql EXTRACT() function) or format (using the mysql DATE_FORMAT() function) it any way you want when you retrieve it.

[ad_ke]

Okay,

This gives me some ideas. The problem I'm having though is that the gmmktime() returns a unix timestamp, and so if I define my MySQL tables as datetime, all that is inserted into the database are a bunch of zeros. However, I think you've given me some new options to try. Thanks for your replies. AK

[ad_ke]

Okay,
So gmmktime() creates a unix timestamp that can't be directly inserted into a mysql datetime field. So I convert that with gmdate() to get one that is like this:

$timestamp = gmmktime($hours + 8, $minutes, $seconds, $month, $day, $year, $isDaylight);
$h_timestamp = gmdate("Y-m-d H:i:s", $timestamp);

But when I run the insert script: $query = 'INSERT INTO raw_time (id, timestamp, h_timestamp, import_id, plot_id, obs_int)
VALUES ("", '.(int)$values['timestamp'].', '.(int)$values['h_timestamp'].', '.(int)$this->importId.', '.(int)$this->plotId.', '.(int)$obsInterval.')'; the resulting mysql datetime field is 0000-00-00 00:00:00. So I'm assuming that the insert syntax is incorrect. Is there a better way to do this?

Thanks,
AK

[ad_ke]

Okay, I'm almost there. I have my function outputting and inserting the mysql timestamp into the database. Now! I'd like to use the gmdate() within the mysql query. Is this possible? It would look something like:

$query = 'INSERT INTO raw_time (id, timestamp, h_time) VALUES
("", '.$values['timestamp'].', '.$values['gmdate("YmDHis" timestamp)'].')';
Where timestamp is the unix timestamp.

Yes, I want to store both versions of the timestamp. Please, if you have a moment, advise me on this. Is it possible?

[oesxyl]

Okay, I'm almost there. I have my function outputting and inserting the mysql timestamp into the database. Now! I'd like to use the gmdate() within the mysql query. Is this possible? It would look something like:

$query = 'INSERT INTO raw_time (id, timestamp, h_time) VALUES
("", '.$values['timestamp'].', '.$values['gmdate("YmDHis" timestamp)'].')';
Where timestamp is the unix timestamp.

Yes, I want to store both versions of the timestamp. Please, if you have a moment, advise me on this. Is it possible?
probably this must be:

$query = 'INSERT INTO raw_time (id, timestamp, h_time) VALUES
("", '.$values['timestamp'].', '.(gmdate("YmDHis",$values['timestamp'])).')';

and h_time type must be some string/char/varchar because that is what gmdate return.

regards

[ad_ke]

Yes! That's it! :D

Thanks a lot!

AK

[oesxyl]

Yes! That's it! :D

Thanks a lot!

AK
you can use strtotime to convert from date/datetime to timestamp and strftime to format a timestamp to what you need.

http://www.php.net/manual/en/function.strtotime.php
http://www.php.net/manual/en/function.strftime.php

storing only gmt timestamp in mysql make the data independent of server location and/or place where the data are created so that you can move them to other servers without any conversion.

regards

[ad_ke]

$timestamp =
gmmktime($hours, $minutes, $seconds, $month, $day, $year, $isDaylight);

$query = 'INSERT INTO raw_time (id, timestamp, h_time)
VALUES ("", '.$values['timestamp'].', '.(gmdate("YmdHis",$values['timestamp'])).')

So I think this is all the important stuff and you're right, the two timestamps are the same (when I compare the output using FROM_UNIXTIME(timestamp)).

Thanks for the links. The problem is that this application that I have is deep and the developer that helped me with it used UnixTime for everything so I really have to keep that field for all the non-critical stuff like logins and user data. The gmdate field what I'll use to query the actual climate data.

[oesxyl]

$timestamp =
gmmktime($hours, $minutes, $seconds, $month, $day, $year, $isDaylight);

$query = 'INSERT INTO raw_time (id, timestamp, h_time)
VALUES ("", '.$values['timestamp'].', '.(gmdate("YmdHis",$values['timestamp'])).')

So I think this is all the important stuff and you're right, the two timestamps are the same (when I compare the output using FROM_UNIXTIME(timestamp)).

Thanks for the links. The problem is that this application that I have is deep and the developer that helped me with it used UnixTime for everything so I really have to keep that field for all the non-critical stuff like logins and user data. The gmdate field what I'll use to query the actual climate data.
if you use two fields for same information you denormalize the table and add useless complication to your code. If you need to have date/time in some format to output on some page you can use strftime to convert the 'timestamp' field and don't need 'h_time'.
another thing, off topic this time, is not a good idea to use reserved words as names for fields and tables( I talk about 'timestamp').

regards

[ad_ke]

Good points and thanks again,
Ak

[ad_ke]

Okay, after stewing this for a few days and not being entirely happy about denormalizing my raw_time table, I think we may have a solution to the limitations of FROM_UNIXTIME. It appears that the mysql function DATE_ADD might be the solution within MySQL. I know there are PHP solutions, but when we're going from database to database using only SQL, this might work. I'm assuming this would return a human readable datetime stamp.

SELECT '1970-01-01 00:00:00' + INTERVAL t1.UnixTimestamp SECOND FROM raw_time AS t1;


Right?

Yep, that works.