Turning Off Timestamp Formatting in MySQL

Question

I have many timestamp columns that I need to use with mixed timezones. The users timezone is set in the language I'm using for the front-end so I need MySQL to return a unix timestamp from a select *. Is there a way to turn off the auto formatting when fetching data from MySQL timestamp columns?

Answer 1

YYYY-MM-DD HH:MM:SS is the default representation for timestamp columns in MySQL. I don't believe you can change that on a global level.

Two options:

1. Instead of doing a SELECT *, do SELECT *, UNIX_TIMESTAMP(your_timestamp_column) AS your_timestamp, which will add a Unix-formatted your_timestamp column to the results.

2. Make a view (CREATE VIEW) for each table that does the same thing, e.g.

   CREATE VIEW your_view AS
     SELECT *, UNIX_TIMESTAMP(your_timestamp_column) AS your_unix_timestamp
     FROM your_table;
   

   Then you can do SELECT * FROM your_view; and get your Unix timestamp without adding anything to your queries.

Reference: UNIX_TIMESTAMP

Answer 2

Yes.

You can wrap the filed in the UNIX_TIMESTAMP function like this:

select UNIX_TIMESTAMP(your_timestamp_column) from your_table;

For more information about this and other mysql data and time functions see: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function%5Funix-timestamp