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

Is there a snippet out there to convert the TIMESTAMP(14) in MySQL to epoc format? I'm asking because I need to run the converted form to run it through POSIX's strftime().

Replies are listed 'Best First'.
Re: Formatting MySQL's TIMESTAMP
by dempa (Friar) on Mar 21, 2002 at 07:28 UTC
    This may become a bit OT, but here goes:

    Use MySQL for this. MySQL has (imho) excellent functions for dealing with misc things, for example date and time. Example:

    SELECT UNIX_TIMESTAMP(timestamp) AS epoch FROM extable;

    This give you the column "timestamp" from the table extable in epoch-format and call the column 'epoch' in the output.

    May I also suggest that you format your date with MySQL? This can easily be done with MySQL's DATE_FORMAT function. Example:

    SELECT DATE_FORMAT(timestamp,"%Y-%d-%m %H:%i:%S") AS mydate FROM extable;

    This would give you the timestamp-column (in output called mydate) in the format: yyyy-mm-dd HH:MM:SS.
[reply]
[d/l]
[select]
      What I'm looking for is more on the lines of "%A %d %B %Y @ %I:%M%p %Z".
[reply]
[d/l]
        Well, apart from the timezone that would in DATE_FORMAT syntax equal:

        %W %d %M %Y @ %I:%i%p

        If you're willing to be a bit database-specific, use UNIX_TIMESTAMP to get the timestamp in epoch-format and then use POSIX::strftime to format your date. 
        # epoch-date in $epoch
use POSIX;
my $formatted_date = 
  POSIX::strftime("%A %d %B %Y @ %I:%M%p %Z",localtime($epoch));

        [download]
        If you want to be as non db-specific as possible, take the advice below and forget UNIX_TIMESTAMP. Just select the timestamp-column and use appropriate date-manipulation module to convert it to epoch-format.
[reply]
[d/l]
[select]
Re: Formatting MySQL's TIMESTAMP
by strat (Canon) on Mar 21, 2002 at 11:10 UTC
    You could do it "manually": 
    use Time::Local;
my $timestamp = "20011130053055"; # timestamp(14)
my $epoch = &ConvertTimestamp14ToEpochSeconds($timestamp);
print $timestamp;

sub ConvertTimestamp14ToEpochSeconds {
  my ($timeStamp) = @_;

  my $unpackPattern = "a4 a2 a2 a2 a2 a2";
  my @dateList = reverse unpack($unpackPattern, $timestamp);
  $dateList[4]--; $dateList[5] -= 1900;

  my $epochSeconds = timelocal(@dateList);
  return ($epochSeconds);
} # ConvertTimestamp14ToEpochSeconds

    [download]
    Just take care of dates > 2038 on 32-bit time systems...

    Best regards,
    perl -le "s==*F=e=>y~\*martinF~stronat~=>s~[^\w]~~g=>chop,print"

[reply]
[d/l]
Re: Formatting MySQL's TIMESTAMP
by jeffenstein (Hermit) on Mar 21, 2002 at 08:42 UTC
    If you want to stick to more standard SQL, you may want to pull the date out as a string (if this is how TIMESTAMP() returns the date), and then parse it with Time::ParseDate.
[reply]

Back to Seekers of Perl Wisdom