I updated and modernized my Javascript Unixtime tool. If you have to work with Unixtimes it's a really handy tool to process them in a more human friendly fashion.

It's common to come across date strings in log files that you want to convert to a Unixtime. Perl has Data::Parse which offers a str2time() function to do this.

use Date::Parse;

my $ut = str2time("Thu, 13 Oct 94 10:13:13 +0700") # 782017993;

I wrote a version of strtotime() in a function that may be more portable. It has the limitation that it does not support timezone strings, but if you don't need them then it is a valid alternative.

I need a Unixtime in Javascript. This is the simplest way I came up with to get that value:

var unixtime = parseInt(new Date().getTime() / 1000);

Update: Newer versions of Javascript (and browsers) now offer Date.now() which returns milliseconds since the epoch with less typing required.

var unixtime = parseInt(Date.now / 1000);

When dealing with dates and MySQL often you have to work data that's in unixtime. If you want to insert data into a MySQL table with a datetime field you can do this:

INSERT INTO MyTable VALUES (MyDateField) (FROM_UNIXTIME(1285695703));

If you have data in MySQL as a datetime and you want to extract it as unixtime do this:

SELECT UNIX_TIMESTAMP(MyDateField) AS MyUnixField FROM MyTable;

There is going to be a leap second today at 1230768000 (2008 December 31, 23h 59m 60s). I updated my Javascript Unixtime converter to include options for UTC time. Happy Leap Seconds!

The date command in Linux is very powerful for converting dates and times. If you have a given time in another timezone, or even UTC, you can convert it to your local timezone with this command:

date -d '2008-05-13 14:00 UTC'

If you want to convert a given time into unixtime just use a date format:

date +%s -d '2008-05-13 14:00 UTC'

Here is a good way to generate a random date that you can use in MySQL.

SELECT FROM_UNIXTIME(RAND() * 2147483648) AS MyDate;

or

INSERT INTO TableName (DateField) VALUES (FROM_UNIXTIME(RAND() * 2147483648));

Here's how you get unixtime from a Postgres date field.

SELECT extract('epoch' from DateField) AS UnixTime FROM Table;

I always end up trying to figure out what a unixtime is in human readable format, or vice versa. I ended up borrowing and updating a tool from captain.at, striping out all the ads and other junk and cleaning it up so it's XHTML compliant. Check out my new and improved javascript unixtime converter.

Just a note of how to get Unixtime in Javascipt.

var date_obj    = new Date;
var unixtime_ms = date_obj.getTime();
var unixtime    = parseInt(unixtime_ms / 1000);

Also a Javascript Unixtime conversion utility.

SQLite handles dates quite strangely. If you create a table and put a date in it, the only way SQLite will take the input is "2004-06-01 12:00:00" anything else it won't recognize as a date and thus none of the date functions will work.

Assuming you have a date entered propely (like above) you get date information from the db like so.

SELECT date(DateField) FROM Table;
SELECT time(DateField) FROM Table;
SELECT datetime(DateField) FROM Table;

If you want unix time (seconds since the epoch) you have to format the output.

SELECT strftime("%s", DateField) FROM Table

However that will return the time in UTC which is probably not what you want (it's not what I wanted). I want it to compensate for my local timezone and thus you have to tell it to use your timezone.

SELECT strftime("%s", DateField, 'localtime') FROM Table

This will go the other way. Take a unixtime, and convert it to a SQLite date format.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Some semi-official SQLite documentation on how it handles dates is availble on their wiki page.