how to select date as an integer in mysql

Question

Is it possible to select a date type in mysql as an integer? output it into a .csv file?

Answer 1

Use UNIX_TIMESTAMP() to convert a date to an integer.

mysql> SELECT UNIX_TIMESTAMP();
        -> 1196440210
mysql> SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
        -> 1196440219

---

To export to a CSV, use SELECT...INTO:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

Answer 2

Yes, it is possible. See the Unix_Timestamp function: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

For exporting to a CSV file, see: http://dev.mysql.com/doc/refman/5.0/en/select-into.html

Answer 3

You can use:

select UNIX_TIMESTAMP(date)
from MyTable

UNIX_TIMESTAMP

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC.

Answer 4

Three possible solutions:

1. Unix timestamp
2. Number that represents the date
3. Days elapsed since an "epoch"

Unix Timestamp

Just as RedFilter posted, unix_timestamp(date) returns the value of the Unix timestamp, which is an unsigned integer that represents the number of seconds elapsed since January 1st, 1970 00:00:00 UTC.

Number that represents the date

In some cases you may need to represent the date as a number; for example: 20130331 for March 31st, 2013. This is simply a formatting issue:

select cast(date_format(date, `%Y%m%d') as int)

Check the reference manual for the cast() and date_format() functions appropriate usage.

Days elapsed since an "epoch"

If you need to count the days elapsed since a particular day (let's say, May 15th, 2011), you will need the datediff() function:

SELECT DATEDIFF('2013-03-31','2011-05-15')

Check the reference manual for the datediff() function appropriate usage.

Hope this helps you.