RE: Obtaining GMT time in mySQL
MySQL: DATETIME vs. INT http://forums.devshed.com/showthread.php?s=&threadid=29754&forumid=4 C:heers! -Original Message- From: DL Neil -- Sent: Tuesday, February 05, 2002 1:28 AM Checking the MySQL manual (6.3.4 Date and Time Functions) UNIX_TIMESTAMP() UNIX_TIMESTAMP(date) ...returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) ...in local time: - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Obtaining GMT time in mySQL
My apologies, what I said in this previous post is incorrect. It appeared as if the time was UTC (except for daylight savings) to me, but it was mearly because of the format string As Paul DuBois quite rightly pointed out is that %h in select FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); returns the hour in 12hour format, I assumed it was in 24hour format which makes it appear close to UTC in my timezone. Thus select FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); does not return GMT time but local time. At 02:13 PM 5/02/2002 +1100, Oliver Mannion wrote: >I am close to a solution: > >select FROM_UNIXTIME(UNIX_TIMESTAMP(), > '%Y %D %M %h:%i:%s %x'); > > >UNIX_TIMESTAMP() returns the number of seconds since '1970-01-01 00:00:00' GMT >Convert this to a date format and you have a GMT date. > >Thanks to Chris Land for pointing the UNIX_TIMESTAMP() function >out to me. > >The only prob is daylight savings. We in NSW, Australia are currently >in daylight savings and the date returned doesn't accomodate for that. >Any one have any ideas why? > >[mysql] > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Obtaining GMT time in mySQL
MySQL: DATETIME vs. INT http://forums.devshed.com/showthread.php?s=&threadid=29754&forumid=4 C:heers! -Original Message- From: DL Neil -- Sent: Tuesday, February 05, 2002 1:28 AM Checking the MySQL manual (6.3.4 Date and Time Functions) UNIX_TIMESTAMP() UNIX_TIMESTAMP(date) ...returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) ...in local time: - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Obtaining GMT time in mySQL
Oliver,
It's early in the morning over here, so that's my excuse for feeling confused...
> >> select FROM_UNIXTIME(UNIX_TIMESTAMP(),
> >> '%Y %D %M %h:%i:%s %x');
> >> UNIX_TIMESTAMP() returns the number of seconds since '1970-01-01
> 00:00:00' GMT
> >> Convert this to a date format and you have a GMT date.
> >> The only prob is daylight savings. We in NSW, Australia are currently
> >> in daylight savings and the date returned doesn't accomodate for that.
> >> Any one have any ideas why?
Checking the MySQL manual (6.3.4 Date and Time Functions)
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
...returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) ...in local time:
The reference value (date) is stated in, or the implicit NOW() is taken in, local time
- NOT GMT.
GMT [sic, UTC] has NO daylight savings component.
The allowance for daylight saving depends upon which timezone the server is set to,
and what time of year the
ToD clock is showing (indeed I'm wondering if there are some systems which allow
user-definition of whether
summer time is ever to be applied).
Is the question (above) ambiguous? Which part is in GMT and which part local? Which
part is adjusted for summer
time and which part is coordinated time?
> Perl seems to work with daylight savings, how is this?
=the processing of times/dates provided by the OpSys is subject to whatever the
application cares to do with it.
PHP will give you the choice to work in local time or GMT. If I take a timestamp or
date field from MySQL, I'll
definitely reformat it before making it available for 'public consumption'. ('they'
don't seem to like
CCYY-MM-DD dates!?)
> >UTC doesn't do daylight savings. One reason is that different places
> >do daylight savings at different times. We here in the Western US, for
> >example, are in standard time right now. In Arizona, they don't do
> >daylight savings at all.
=indeed. The whole reason for Universal Coordinated Time was so that there would be a
reference-time 'zone' that
is rigorously continuous. It is defined atomic clocks, not on astronomical
position/variation - or some
convenience of human interpretation of such.
=Regards,
=dn
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Obtaining GMT time in mySQL
Using Perl: my $GMTdate = gmtime(time); this will give you the GMT time. >>On Tue, 05 Feb 2002 15:27:18 +1100, Oliver Mannion <[EMAIL PROTECTED]> wrote: >>Right. >> >>Perl seems to work with daylight savings, how is this? >> >>At 08:03 PM 4/02/2002 -0800, James Montebello wrote: >>> >>>UTC doesn't do daylight savings. One reason is that different places >>>do daylight savings at different times. We here in the Western US, for >>>example, are in standard time right now. In Arizona, they don't do >>>daylight savings at all. >>> >>>james montebello >>> >>>On Tue, 5 Feb 2002, Oliver Mannion wrote: >>> I am close to a solution: select FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); UNIX_TIMESTAMP() returns the number of seconds since '1970-01-01 >>00:00:00' GMT Convert this to a date format and you have a GMT date. Thanks to Chris Land for pointing the UNIX_TIMESTAMP() function out to me. The only prob is daylight savings. We in NSW, Australia are currently in daylight savings and the date returned doesn't accomodate for that. Any one have any ideas why? [mysql] Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Obtaining GMT time in mySQL
Right. Perl seems to work with daylight savings, how is this? At 08:03 PM 4/02/2002 -0800, James Montebello wrote: > >UTC doesn't do daylight savings. One reason is that different places >do daylight savings at different times. We here in the Western US, for >example, are in standard time right now. In Arizona, they don't do >daylight savings at all. > >james montebello > >On Tue, 5 Feb 2002, Oliver Mannion wrote: > >> I am close to a solution: >> >> select FROM_UNIXTIME(UNIX_TIMESTAMP(), >> '%Y %D %M %h:%i:%s %x'); >> >> >> UNIX_TIMESTAMP() returns the number of seconds since '1970-01-01 00:00:00' GMT >> Convert this to a date format and you have a GMT date. >> >> Thanks to Chris Land for pointing the UNIX_TIMESTAMP() function >> out to me. >> >> The only prob is daylight savings. We in NSW, Australia are currently >> in daylight savings and the date returned doesn't accomodate for that. >> Any one have any ideas why? >> >> [mysql] >> >> - >> Before posting, please check: >>http://www.mysql.com/manual.php (the manual) >>http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Obtaining GMT time in mySQL
UTC doesn't do daylight savings. One reason is that different places do daylight savings at different times. We here in the Western US, for example, are in standard time right now. In Arizona, they don't do daylight savings at all. james montebello On Tue, 5 Feb 2002, Oliver Mannion wrote: > I am close to a solution: > > select FROM_UNIXTIME(UNIX_TIMESTAMP(), > '%Y %D %M %h:%i:%s %x'); > > > UNIX_TIMESTAMP() returns the number of seconds since '1970-01-01 00:00:00' GMT > Convert this to a date format and you have a GMT date. > > Thanks to Chris Land for pointing the UNIX_TIMESTAMP() function > out to me. > > The only prob is daylight savings. We in NSW, Australia are currently > in daylight savings and the date returned doesn't accomodate for that. > Any one have any ideas why? > > [mysql] > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Obtaining GMT time in mySQL
I am close to a solution: select FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x'); UNIX_TIMESTAMP() returns the number of seconds since '1970-01-01 00:00:00' GMT Convert this to a date format and you have a GMT date. Thanks to Chris Land for pointing the UNIX_TIMESTAMP() function out to me. The only prob is daylight savings. We in NSW, Australia are currently in daylight savings and the date returned doesn't accomodate for that. Any one have any ideas why? [mysql] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Obtaining GMT time in mySQL
You *could* use UNIX_TIMESTAMP(NOW()), which will return an integer timestamp from the server in standard Unix time, which is the number of seconds since 00h00 1 Jan 1970 UTC. However, there is no easy way to get this to display in -MM-DD HH:MM:SS format w/o the conversion to local time taking place. I also have no idea if this will work correctly on a non-Unix platform, as I've never used MySQL on anything but Linux and Solaris. Setting the TZ variable on the server will the right thing, but all times on the box will be in UTC, not just some of them, which is probably not what you want. james montebello On Tue, 5 Feb 2002, Oliver Mannion wrote: > Hi dn, > > Right. Having said that tho, is there a function > in mysql that will return the GMT time from the operating > system, that I can subsequently use in a query. > I could use the GMT time from the db client but I > would prefer to use the GMT time from the operating > system mySQL is running on. > > Thanks > Oliver > > At 12:35 AM 5/02/2002 +, DL Neil wrote: > >Hi Oliver, > > > >> I would like to obtain the time in GMT in mySQL. > >> > >> CURRENT_TIMESTAMP returns the local time > >> SHOW VARIABLES returns a list of variables, one being TZ which is "EST" on > >> my system > >> > >> But how can I get the time in GMT??? > > > > > >MySQL is time-neutral, that is to say, if you ask it the time, it will ask > the server OpSys. > > > >As my mother used to say, "you only get out of something, what you put > into it"! If you declare a date/time > >column, any data you INSERT will have the same value when you subsequently > SELECT it. It's up to you to > >determine which time zone to use when representing your data. > > > >Are you accessing MySQL from some other facility, eg PHP? Can you use that > tool to acquire/restate the temporal > >data? > > > >Regards, > >=dn > > > > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Obtaining GMT time in mySQL
Hi dn, Right. Having said that tho, is there a function in mysql that will return the GMT time from the operating system, that I can subsequently use in a query. I could use the GMT time from the db client but I would prefer to use the GMT time from the operating system mySQL is running on. Thanks Oliver At 12:35 AM 5/02/2002 +, DL Neil wrote: >Hi Oliver, > >> I would like to obtain the time in GMT in mySQL. >> >> CURRENT_TIMESTAMP returns the local time >> SHOW VARIABLES returns a list of variables, one being TZ which is "EST" on >> my system >> >> But how can I get the time in GMT??? > > >MySQL is time-neutral, that is to say, if you ask it the time, it will ask the server OpSys. > >As my mother used to say, "you only get out of something, what you put into it"! If you declare a date/time >column, any data you INSERT will have the same value when you subsequently SELECT it. It's up to you to >determine which time zone to use when representing your data. > >Are you accessing MySQL from some other facility, eg PHP? Can you use that tool to acquire/restate the temporal >data? > >Regards, >=dn > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Obtaining GMT time in mySQL
Hmmm - pardon me for jumping in in a conversation in progress - but according to the manual* you should be able to set the TZ variable to your timezone - which sounds like what has happened on your machine. If you can figure out how to put the right code in, your SQL queries will purportedly be in GMT. I would try safe_mysqld --timezone=0 Or safe_mysqld --timezone=GMT I'm trying to figure out something similar (see my post below) - so let me know if you find anything 'deep *http://www.mysql.com/doc/s/a/safe_mysqld.html & http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html -my post Hi folks - I am having 2 related problems with the timezone setting for mysqld & was hoping to get some help. Basically my squestions are as follows: a) the documentation says to set timezone by simply using --timezone=# when starting mysqld - but WHAT are the valid number values & which time zones do the map to? b) I have tried to set the timezone using "safe_mysqld --timezone=XX" where I have tried various values for XX such as o1, 1, 13, 05,08 BUT near as I can tell ANY value I put in gives me GMT/UTC time (I think that's what it is) rather than giving me time offset by the given amount i entered. This is not what the documentation seems to suggest will happen - or am I being obtuse? Any help or suggestions would be appreciated. -- Amandeep Jawa Worker Bee Software -- [EMAIL PROTECTED] 225A Dolores St. San Francisco, CA 94103-2202 Home: 415 255 6257 (ALL MALP) professional: http://www.worker-bee.com personal: http://www.deeptrouble.com political: http://www.sflcv.org > From: "DL Neil" <[EMAIL PROTECTED]> > Reply-To: "DL Neil" <[EMAIL PROTECTED]> > Date: Tue, 5 Feb 2002 00:35:28 -0000 > To: <[EMAIL PROTECTED]>, "Oliver Mannion" <[EMAIL PROTECTED]> > Subject: Re: Obtaining GMT time in mySQL > > Hi Oliver, > >> I would like to obtain the time in GMT in mySQL. >> >> CURRENT_TIMESTAMP returns the local time >> SHOW VARIABLES returns a list of variables, one being TZ which is "EST" on >> my system >> >> But how can I get the time in GMT??? > > > MySQL is time-neutral, that is to say, if you ask it the time, it will ask the > server OpSys. > > As my mother used to say, "you only get out of something, what you put into > it"! If you declare a date/time > column, any data you INSERT will have the same value when you subsequently > SELECT it. It's up to you to > determine which time zone to use when representing your data. > > Are you accessing MySQL from some other facility, eg PHP? Can you use that > tool to acquire/restate the temporal > data? > > Regards, > =dn > > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Obtaining GMT time in mySQL
Hi Oliver, > I would like to obtain the time in GMT in mySQL. > > CURRENT_TIMESTAMP returns the local time > SHOW VARIABLES returns a list of variables, one being TZ which is "EST" on > my system > > But how can I get the time in GMT??? MySQL is time-neutral, that is to say, if you ask it the time, it will ask the server OpSys. As my mother used to say, "you only get out of something, what you put into it"! If you declare a date/time column, any data you INSERT will have the same value when you subsequently SELECT it. It's up to you to determine which time zone to use when representing your data. Are you accessing MySQL from some other facility, eg PHP? Can you use that tool to acquire/restate the temporal data? Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
