Showing entries with tag "Unixtime".
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;
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;
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;
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');