Funciones MySQL para convertir timestamp a fecha

En el siguiente artículo comentaré cómo pasar la representación de una fecha en timestamp de unix a formato de tipo ‘YYYY-MM-DD HH:MM:SS’ mucho más legible y con el que podremos realizar consultas mas específicas.

En base de datos se suele guardar las fechas en formato timestamp pues contienen algo más de información que si por defecto formateamos y guardamos la fecha en formato ‘YYYY-MM-DD HH:MM:SS’ o ‘YYYY-MM-DD’. Para obtener el timestamp de una fecha en PHP usaremos la función time(), con la que obtendremos el timestamp de la fecha del momento en que se ejecuta.

La función de MySQL que realiza la conversión de timestamp a fecha es: FROM_UNIXTIME(unix_timestamp) en la que sólo se la pasa el timestamp y el formato que devuelve es el de por defecto: ‘YYYY-MM-DD HH:MM:SS’.

También se le puede especificar un formato para deveolver tan sólo lo que queramos FROM_UNIXTIME(unix_timestamp,format). Veamos algunos ejemplos:

Queremos tan sólo el año

select from_unixtime(fecha_creacion,'%Y') as fecha from table group by fecha

Resultado:

fecha

2012

Queremos el siguiente formato: mes. día.año

select from_unixtime(fecha_creacion,'%m.%d.%Y') as fecha from table group by fecha

Resultado:

fecha

07.06.2012

07.11.2012

Si esto mismo lo queremos poner en formato español con el mes en medio, tan sólo cambiaremos el especificador del mes(%m) de posición

select from_unixtime(fecha_creacion,'%d.%m.%Y') as fecha from table group by fecha

Resultado:

fecha

06.07.2012

11.07.2012

También podemos indicar un formato de fecha y con la hora separado por dos puntos “:”

select from_unixtime(fecha_creacion,'%Y-%m-%d %H:%i:%s') as fecha from table group by fecha

Resultado:

fecha

2012-07-06 08:12:50

2012-07-11 09:09:28

Para todo el que quiera hacer pruebas y obtener éstos mismos resultados los timestamps que he utilizado son los siguientes:  1341555170 y 1341990568

Tabla con todos los especificadores de formatos de Fecha

En la siguiente tabla se indican todos los formatos que se pueden asignar para tratar una fecha en timestamp

Fuente:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_from-unixtime

Herramienta útil: CONVERSOR ONLINE TIMESTAMP A FECHA