how to calculate time that looks like 1138597506

[Anonymous Monk]

I'm trying to make a work around for my forum on my site. In mysql it stores time as something that looks like 1138597506, I forgot the true name of this.

Since I can't read it, it makes it hard for me to make changes to it.

I want to create a very simple CGI script that will convert a time I put in back to the seconds. I'm thinking pulldowns for the day/month/year and hour/min/sec would be the easiest choice but I don't know how I'd start to convert 6 vars back into seconds.

Can anyone help me get started?

[ikegami]

It's called epoch time, the number of seconds since Jan 1st, 1970.

>perl -le "print scalar gmtime 1138597506
Mon Jan 30 05:05:06 2006

To convert to epoch time, you can use core module Time::Local's timegm or timelocal.

To make changes, you should use Date::Calc.

To render into a human-readable format, POSIX's strftime (in conjunction with localtime or gmtime) should do the trick.

Time::ParseDate might be of use if you want to avoid the dropdowns.

Update: Rearranged order of text. Added last paragraph.

[chargrill]

$ perl -e 'print localtime( 1138597506 ). "\n"'
Sun Jan 29 23:05:06 2006

$perl -e 'print join " ", localtime( 1138597506 ), "\n"'
6 5 23 29 0 106 0 28 0

In scalar context, passing your value to localtime will give you a nice textual representation. In list context, it gives you a list you can format on your own, with the values being Seconds, Minutes, Hours, (0 based) Day of Month, (0 based) Month, Year (minus 1900), (0 based) Day of Week, (0 based) Day of Year, and whether or not it is in DST.

Or something like that. perldoc -f localtime will correct any inaccuracies :)

Update: I read too fast. ikegami's right on the money. Excerpted from perldoc Time::Local:

These routines are the inverse of built-in perl functions localtime() and gmtime(). They accept a date as a six-element array, and return the corresponding time(2) value in seconds since the system epoch (Mid- night, January 1, 1970 GMT on Unix, for example). This value can be positive or negative, though POSIX only requires support for positive values, so dates before the system's epoch may not work on all operat- ing systems.

[davorg]

That's the time given as the number of seconds since midnight, Jan 1st 1970 GMT (known as the epoch). Perl will give the current value for that number using the time function.

You can convert from that number back to a more human-understandable representation using localtime or gmtime.

You can create one of these "epoch seconds" numbers for a given date and time using the timelocal or timegm functions from the Time::Local module.

[polettix]

In your particular case, you can do both from/to that format inside MySQL:

mysql> SELECT FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300
mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2003 6th August 06:22:58 2003'


mysql> SELECT UNIX_TIMESTAMP();
        -> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
        -> 875996580

See the linked doc page for details and other examples. I would also suggest to store dates using one of the native date formats, like datetime.

[sauoq]

I want to create a very simple CGI script

CGI? Why? This alias works for me.

alias unixtime="perl -MTime::ParseDate -le'print parsedate(\"@ARGV\", 
+ZONE=>\"GMT\")'"

Call it like:

$ unixtime Thu Feb  8 15:28:00 2007

or

$ unixtime 2/8/07 3:28pm

or

$ unixtime 2-8-2007 15:28

or

$ unixtime `date`

or ... well, you get the idea. It's pretty good at figuring out what you mean.