Epoch to Mysql datetime format

Snipet of code converts an epoch time to a MySQL datetime format. (Datetime format is : YYYY-MM-DD HH24:MI:SS ) UPDATE: Do NOT use this code snipet, instead use one of the techniques below.

sub getMysqlTime($){
  my $val = shift;
  my @ary = gmtime($val);
  my $year = $ary[5] +1900;
  my $month = $ary[4] + 1;
  my $day = $ary[3];
  my $hour=$ary[2];
  my $min=$ary[1];
  my $sec= $ary[0];
  my $time = sprintf("%04i-%02i-%02i %02i:%02i:%02i",$year,$month,$day
+,$hour,$min,$sec);
    return($time);
}
[download]

Comment on Epoch to Mysql datetime format - DO NOT USE Download Code

Replies are listed 'Best First'.
Re: Epoch to Mysql datetime format by b10m (Vicar) on Mar 10, 2004 at 13:56 UTC
I'm not sure wheter you use this to insert epoch values into a MySQL date_time field, if so, why all the hassle? You could easilly use some SQL like this: `INSERT INTO foo VALUES(FROM_UNIXTIME(1078926984));` [download] -- b10m All code is usually tested, but rarely trusted.	[reply] [d/l]
Re: Re: Epoch to Mysql datetime format by zakzebrowski (Curate) on Mar 10, 2004 at 15:26 UTC
Excellent, thanks. I was unaware of this feature in mysql. (I tend to be more of an oracle sql user than anything else, though I use mysql as a 'staging' area before code is released.) I'll use this in the future. ---- Zak - the office	[reply]
Re: Epoch to Mysql datetime format by valdez (Monsignor) on Mar 10, 2004 at 13:58 UTC
Your code could be rewritten as: `use POSIX qw( strftime ); sub getMysqlTime { return POSIX::strftime("%Y-%m-%d %H:%M:%S", gmtime(shift)); }` [download] There is also Time::Piece::MySQL that does the same. Ciao, Valerio	[reply] [d/l]
Re: Epoch to Mysql datetime format by Juerd (Abbot) on Mar 10, 2004 at 13:56 UTC
Alternatively, `use POSIX qw(strftime); sub mysqltime (;$) { strftime '%Y-%m-%d %H:%M:%S', localtime shift }` [download] Juerd # { site => 'juerd.nl', plp_site => 'plp.juerd.nl', do_not_use => 'spamtrap' }	[reply] [d/l]