date conversion gmt<-> system

[Anon]

Hi!
I'm filling a mySQL database with data from different servers in different time zones. I then want to use this data to show on my pages in a uniform time zone, so i can easily compare them. I played around a bit with date, gmdate, mktime etc, but currently my mind is blocked. I put the date and time into one char(14) field with the format yyyymmddhhmms, but i can change that rather easily if necessary. But if anyone could point out if I can convert every time I insert into GMT and then back easily that would be very nice. If you also know how to do this in Perl (I havn't checkd on that yet, but I use some perl scripts to paste the data into the db, too), then you are godlike =). I havn't figured out what gmmtime does, it seems to generate the same output as mktime.
any help appreciated!
mats99

[Anon]

Hi!
I just wanted to add I read the other posts about that, and my server is not giving me GMT, but the local time where it is located. So i also have to compensate that.
I may also add for completeness that I want to print the date and time in two different time zones (european & us), so
I can think of using 3 variables serveroffset, us-offset, europe-offset, each containing the difference between GMT and the according time.
Is there any nice way to go here or do I just have to to all calculation manually ?

[Anon]

Why do you invent your own storage type in the database system? Use the database system's own date/time facilities; that makes it easier to work with your data.

Also: Make sure that you learn how to use the unix timestamp system (number of seconds since Jan 1, 1970 - very handy, actually). The unix timestamp is a highly "neutral" way to work with time.

Finally:
MySQL has some handy functions which you may use in your SQL:
unix_timestamp()
from_unixtime()

They are described in MySQL's manual.

Other than that, it's hard to help because you are not very precise about what coding-problems you need to solve.

[Anon]

Hi,

I was wondering if you found a solution to the problem - my server is running GMT and I need to reliably write EST to a database table.

The other guy (Troels Arvin) has misunderstood the question.

Bob

[Anon]

Hi!

As far as I can remember I havn't got any real good answer to that question, but I gave up quickly because the project had to be finished, so I manually calculated each date I inserted back to GMT and then added the offset again when I printed them. So I could at least be sure that all dates in the database are GMT.

Also, I used mysql's DATE field instead of my own 10 char text field later on cause I could convert that more easily with php's and mysql's functions.

mats