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
| Especificador | Descripción |
|---|---|
| %a | Día de la semana abreviado (Sun..Sat) |
| %b | Nombre del mes abreviado (Jan..Dec) |
| %c | Mes en número (0..12) |
| %D | Día del mes con el sufijo en inglés (0th, 1st, 2nd,3rd, …) |
| %d | Día del mes en número (00..31) |
| %e | Día del mes en número (0..31) |
| %f | Microsegundos (000000..999999) |
| %H | Hora (00..23) |
| %h | Hora (01..12) |
| %I | Hora (01..12) |
| %i | Minutos (00..59) |
| %j | Día del año (001..366) |
| %k | Hora (0..23) |
| %l | Hora (1..12) |
| %M | Nombre del mes (January..December) |
| %m | Número del mes (00..12) |
| %p | AM or PM |
| %r | Fecha, 12 horas (hh:mm:ss seguido de AM or PM) |
| %S | Segundos (00..59) |
| %s | Segundos (00..59) |
| %T | Fecha, 24 horas (hh:mm:ss) |
| %U | Semana (00..53), donde Domingo es el primer día de la semana |
| %u | Semana (00..53), donde Lunes es el primer día de la semana |
| %V | Semana (01..53), donde Domingo es el primer día de la semana; usado con %X |
| %v | Semana (01..53), donde Lunes es el primer día de la semana; usado con %x |
| %W | Nombre del día de la semana (Sunday..Saturday) |
| %w | Día de la semana (0=Domingo..6=Sábado) |
| %X | Año para la semana donde Domingo es el primer día de la semana, numérico de 4 dígitos; usado con %V |
| %x | Año para la semana donde Lunes es el primer día de la semana, numérico de 4 dígitos; usado con %v |
| %Y | Año, numérico de 4 dígitos |
| %y | Año numérico de 2 dígitos |
| %% | Carácter literal “%” |
| %x | x, para cualquier “x” no mencionados anteriormente |
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