Updated my Javascript Unixtime tool
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.
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.
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's how you get unixtime from a Postgres date field.
SELECT extract('epoch' from DateField) AS UnixTime FROM Table;
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.
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.
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.
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!
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));