As a general rule, when having to deal with datetimes and timezones, NEVER store a localtime. If you are going to have users / processing in multiple timezones, the sanest way to deal with everything is to store it ALL in UTC / GMT from the outset.

Storing UTC makes it trivial to display correct localised date-times in Perl / various web and other front ends / languages etc using standard libraries, and without adding the overhead of your own complex date-math for all accesses to the data.

As an aside, MySQL <= 4.1 is a PITA for saving UTC - all the time functions are localtime, and affected by the TZ of your mysql daemon.

Use this expression for a UTC now() in MySQL: 

  update mytable
  set right_now=DATE_ADD('1970-01-01', INTERVAL UNIX_TIMESTAMP() SECON
+D)

[download]
To get correct localtimes in Perl / etc, just make sure that you set the TZ of the process handling the request, and the localtime functions will do the hard work for you.

Regards,

Jeff

In reply to Re: Finding a remote time by jaa
in thread Finding a remote time by Moron

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.