Need to convert MySQL timestamp to local UTC timestamp?

PHP
1

Gurus! I need your help. :slight_smile:

My MySQL database returns a timestamp formatted like so:

1239752639

When testing:


echo date('m/d/y, g:ia', 1239752639) . "\
"; // DB
echo 'VS' . "\
";
echo date('m/d/y, g:ia', time()) . "\
"; // Current.

The above outputs:


04/14/09, 4:43pm
VS
04/14/09, 10:37am

As you can see, the DB time is ahead of the local time.

I am sure that I am missing something obvious here, but what is the best way to convert the database timestamp into a local UTC timestamp?

Many thanks in advance! :smiley:

Cheers,
Micky

2

I don’t know what’s up with the 6 minutes, but if it’s 6 hours ahead of your preferred timezone, subtract 6 hours.

$timestamp = $timestamp - (6 * 60 * 60);
3

Hi Dan!

Thanks for the reply, I really really really appreciate the help. :slight_smile:

$timestamp - (6 * 60 * 60);

Ahhh, that simple! Nice. :slight_smile:

I guess I was assuming that I should account for the timezone diff by using the server.

Should I worry about daylight savings time?

Man, this time/date stuff makes my head spin!

Anyway, your solution will work great! :tup: :spf:

You ROCK Dan!!!

Edit:

I wonder if I should be doing the time conversion via the sql? “SELECT last_entry_date …”

Cheers,
Micky

4

Hi!

Just as an update, the code Dan provided works perfectly:

<?php $timestamp - (7 * 60 * 60); ?>

Thanks again Dan. :slight_smile:

5

MySQL has timezone conversion functions, you can do it there as well.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz

6

Oooh, cool! Thanks again Dan!

I definitely needed a kick in the right direction on this one. :slight_smile:

Have a great day!
Cheers,
Micky

7

In MySQL, is the timestamp field of the “timestamp” type?
If so, MySQL timestamps are not the same as the UNIX timestamps that PHP works with.

To convert it in the sql, you can do something like this:

$result = mysql_query("
	SELECT
		UNIX_TIMESTAMP(timestamp_field) AS unix_time
	FROM
		table
	WHERE
		.........
");
$row = mysql_fetch_assoc($result);
echo date('m/d/y, g:ia', $row['unix_time']) . "\
"; // DB
echo 'VS' . "\
";
echo date('m/d/y, g:ia', time()) . "\
"; // Current.
8

He gave an example of what his query is returning, 1239752639, which is already a UNIX timestamp for today.

9

Hi simshaun!

Ahhh, very interesting! Thanks for the code and clarification! I definitely learn best by first-hand example. :slight_smile:

:tup: :spf:

That is what I assumed they are… I am using a CMS called Expression Engine, and I guess it stores dates in the DB in the (what I assumed was) UTC format “1239752639”.

The EE CMS handles a the TZ conversion for almost all my needs. Just, in this instance, I had to break out of the EE template code and use a custom query.

Sorry if I used any of the wrong terms for this date stuff… Like I said, it makes my head spin! :smiley:

10

Great choice of CMS. EE is favorite next to my own. :slight_smile:

Anyway, EE stores all timestamps in UTC(GMT) using an INT field in MySQL.
So, all you need to do is offset the time by however many hours you are away from UTC.

See this entry for EE’s date localization.

11

Ah, cool! Yah, I love EE… I use Textpattern as my second choice. Do you have a link to your CMS?

Oh, great info!

This is the first time I have had to really go outside of EE templating system to get the data I need…

Anyway, thanks thanks thanks!!! :smiley:

12

Do you have a link to your CMS?
My CMS isnt public. Its sort of an off-shoot of EE, minus some features most of my clients never need, plus some others. I’m already well into development on a new one too, which incorporates some of my favorite aspects of EE with some other CMSs I’ve reviewed. :slight_smile:

13

Ahh, very cool! Built using Code Igniter? I see you are linking to CI in your sig. Looks like a nice framework. I have been using EE for years, and (in last few years) have wondered how much better, or different, the Elis Lab framework is…

I think for my next personal website I will try using a PHP/Python (Django) framework. As much as I love EE and TXP, I would really prefer something more homegrown and lightweight.

Thanks again simshaun and Dan! I owe you guys a brewski! :drink:

14

The new one I’m creating is built on top of the CI framework and heavily utilizes the jQuery/jQuery UI framework.

CI really is an excellent framework, and I’d recommend it to anybody in a heartbeat.
ExpressionEngine 2.0, which is under development, is built on top of the CI framework.
I am anxious to see how well it performs when it comes out.

Another CMS that I think is pretty cool, but I like less than EE, is ModX.

15

Ooooh, so cool! Yah, it probably would be smart for me to start using the framework so I can be on top of my EE game for when 2.0 comes out! I have been looking forward to v2.

I also love jQuery (who doesn’t???) :smiley:

Hehe, you have inspired me to learn something new. Thanks!!! :nanaman:

Oh, yah! I have heard good things about that CMS also! Thanks for the recommendation. :slight_smile:

Thanks simshaun!

Cheers,
Micky