0

How do you set in MySQL the timestamp type column to be returned as integer by default?

To specify:

I know that I can query

select UNIX_TIMESTAMP(timestamp_column) from table ...,

but when I query

select * from table ...

How can I force timestamp_column to be returned as int, and not as YYYY-MM-DD HH:ii:ss, in every query like the previous one?

Is there a query like set names utf8 that I can execute to make the described behaviour default?

Manashvi Birla
  • 2,837
  • 3
  • 14
  • 28
Antti
  • 113
  • 9
  • if the datatype is `timestamp` then doing just `select col1,col2...` should return the timestamp col value as `YYYY-MM-DD H:i:s` – Abhik Chakraborty Jun 03 '15 at 07:11
  • Unfortunately this is not the solution. I don't want it as YYYY-MM-DD HH:ii:ss but by default as **integer** – Antti Jun 03 '15 at 07:13
  • Then you are left with no choice but to use `UNIX_TIMESTAMP` as far as I know. – Abhik Chakraborty Jun 03 '15 at 07:16
  • If this is so (well I have been reading the MySQL documentation a lot for this), as well as I believe too, I think it is a shortcoming in MySQL. – Antti Jun 03 '15 at 07:22
  • No that’s not a shortcoming , the datetime, timestamp values are stored in the proper format in the DB. When you do queries with date its easy to use different functionality. However while selecting and getting data as integer value is of certain requirement and for this you have a function `UNIX_TIMESTAMP` – Abhik Chakraborty Jun 03 '15 at 07:25
  • For the shortcoming, I meant only that default return type for timestamp cannot be set. Naturally, the benefits of having timestamp type column are evident. – Antti Jun 03 '15 at 07:30
  • In SQL server, a generated or computed column solves this problem, but not in MYSQL. Look here on how to use triggers as a work around: http://stackoverflow.com/questions/5422729/mysql-computed-column – Jim Mc Jun 03 '15 at 18:18

0 Answers0