rendler has asked for the wisdom of the Perl Monks concerning the following question:

I was wonder how I should be storing dates for things in a database (user creation, message creation) so that it can be easily manipulated for a particular timezone, such as PM has here. So far I've tried using time() and fiddling around with setting $ENV{TZ} then using POSIX::strftime to format the date. But this seems like the wrong way to go about doing something like this.

Replies are listed 'Best First'.
Re: Bottling time
by RMGir (Prior) on Jul 31, 2002 at 12:15 UTC
    Like everything else Date or Time-like, Date::Manip handles that quite well, I think. 
           Date_ConvTZ
            $date=&Date_ConvTZ($date);
            $date=&Date_ConvTZ($date,$from);
            $date=&Date_ConvTZ($date,"",$to);
            $date=&Date_ConvTZ($date,$from,$to);

           This converts a date (which MUST be in the format
           returned by ParseDate) from one timezone to another.

           If it is called with no arguments, the date is con­
           verted from the local timezone to the timezone speci­
           fied by the config variable ConvTZ (see documentation
           on ConvTZ below).  If ConvTZ is set to "IGNORE", no
           conversion is done.

           If called with $from but no $to, the timezone is con­
           verted from the timezone in $from to ConvTZ (of TZ if
           ConvTZ is not set).  Again, no conversion is done if
           ConvTZ is set to "IGNORE".

           If called with $to but no $from, $from defaults to
           ConvTZ (if set) or the local timezone otherwise.
           Although this does not seem immediately obvious, it
           actually makes sense.  By default, all dates that are
           parsed are converted to ConvTZ, so most of the dates
           being worked with will be stored in that timezone.

           If Date_ConvTZ is called with both $from and $to, the
           date is converted from the timezone $from to $to.

           NOTE: As in all other cases, the $date returned from
           Date_ConvTZ has no timezone information included as
           part of it, so calling UnixDate with the "%z" format
           will return the timezone that Date::Manip is working
           in (usually the local timezone).

           Example:  To convert 2/2/96 noon PST to CST (regard­
           less of what timezone you are in, do the following:

            $date=&ParseDate("2/2/96 noon");
            $date=&Date_ConvTZ($date,"PST","CST");

           Both timezones MUST be in one of the formats listed
           below in the section TIMEZONES.

    [download]
    But read the caveats in the D::M docs; it might be slower than other solution.
    --
    Mike
[reply]
[d/l]
Re: Bottling time
by fireartist (Chaplain) on Jul 31, 2002 at 13:17 UTC
    For storing the dates in a database, I would use 'datetime' columns, which can be set using the databases own now() function.
    Using datetime column types will allow you to do datetime calculations within your 'where' query.
    Also note the funky 'timestamp' column which will update the recorded datetime automatically whenever it's changed or read.

    read more in the mysql.com documentation.
[reply]
[d/l]
      In Oracle the datatype is just DATE, and you need to select SYSDATE instead of now(). Precision is down to the second, anything more than that and you should start storing numbers. You'll probably use the TO_DATE() and TO_CHAR() functions to manipulate them...

      I'd store the date and have another column that maps the user's local timezone setting so that you can convert the time for display.
[reply]
Re: Bottling time
by simeon2000 (Monk) on Jul 31, 2002 at 12:56 UTC
    Well, I would definitely store the UNIX datestamp in the database with the time() function as an unsigned integer or maybe a bigint.

    But if you don't have Date::Manip as suggested above, the code scalar(localtime($time)) prints out a very nicely formatted date string in the same way that the UNIX date command does.

    --
    <code>perl -e "print qq/just another perl hacker who doesn't grok japh\n/"

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom