Archivo de la categoría: SQL*Plus

Conversión entre zonas horarias

Deja un comentario

La conversión de fechas y horas entre zonas horarias es una tarea delicada. Los distintos husos horarios no son un dato estático y hay que tener en cuenta si las zonas horarias entre las que queremos convertir horas mantienen el cambio de hora en verano y en qué fecha lo hacen cada año.

Oracle tiene una serie de funciones para tratar fechas que nos pueden resolver este problema. Las siguientes consultas, por ejemplo, convierten una hora en GMT-0 a los husos horarios de Madrid, Chicago y Bogotá en distintos momentos del año. Hemos elegido estas tres ciudades para representar las distintas casuísticas que se pueden presentar, ya que cuentan con distintas fechas de finalización del horario de verano o, como en el caso de Bogotá, zonas que nunca aplican este cambio:

  • Madrid: Domingo, 27 octubre 2013, 03:00
  • Chicago: Domingo, 03 noviembre 2013, 02:00
  • Bogotá no realiza el cambio de horario
-- Europe/Madrid and America/Chicago Daylight Saving Time
select FROM_TZ( to_timestamp('01-Oct-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'Europe/Madrid' as "Madrid hour",
       FROM_TZ( to_timestamp('01-Oct-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Chicago' as "Chicago hour",
       FROM_TZ( to_timestamp('01-Oct-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Bogota' as "Bogota hour"
from dual;

-- Only America/Chicago Daylight Saving Time
select FROM_TZ( to_timestamp('01-Nov-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'Europe/Madrid' as "Madrid hour",
       FROM_TZ( to_timestamp('01-Nov-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Chicago' as "Chicago hour",
       FROM_TZ( to_timestamp('01-Nov-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Bogota' as "Bogota hour"
from dual;

-- None Daylight Saving Time
select FROM_TZ( to_timestamp('01-Dic-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'Europe/Madrid' as "Madrid hour",
       FROM_TZ( to_timestamp('01-Dic-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Chicago' as "Chicago hour",
       FROM_TZ( to_timestamp('01-Dic-2013 03:11:00.123000', 'DD-Mon-YYYY HH24:MI:SS.FF'), 'Etc/GMT-0') AT TIME ZONE 'America/Bogota' as "Bogota hour"
from dual;

El resultado debería ser algo similar al siguiente:

Madrid hour (Daylight Saving Time)      Chicago hour (Daylight Saving Time)     Bogota hour (Never)
-------------------------------------   ------------------------------------    ------------------------------------
01/10/2013 5:11:00,123000000 +02:00     30/09/2013 22:11:00,123000000 -05:00    30/09/2013 22:11:00,123000000 -05:00

Madrid hour (No Daylight Saving Time)   Chicago hour (Daylight Saving Time)     Bogota hour (Never)
-------------------------------------   ------------------------------------    ------------------------------------
01/11/2013 4:11:00,123000000 +01:00     31/10/2013 22:11:00,123000000 -05:00    31/10/2013 22:11:00,123000000 -05:00

Madrid hour (No Daylight Saving Time)   Chicago hour (NO Daylight Saving Time)  Bogota hour (Never)
-------------------------------------   ------------------------------------    ------------------------------------
01/12/2013 4:11:00,123000000 +01:00     30/11/2013 21:11:00,123000000 -06:00    30/11/2013 22:11:00,123000000 -05:00

Para obtener el nombre y abreviatura de las distintas zonas horarias que contempla Oracle solo tenemos que lanzar la siguiente consulta:

select TZNAME, TZABBREV, tz_offset(TZNAME)
from v$timezone_names;

Más información sobre soporte a la globalización y husos horarios en Oracle 11g:

http://docs.oracle.com/cd/E11882_01/server.112/e10729/applocaledata.htm#i637736

Anuncio publicitario

Volcado de una consulta SQL a fichero (.csv)

Deja un comentario

Mediante SQL*Plus se pueden realizar volcados de datos a fichero dando el formato que nos sea más conveniente. Todo depende de la parametrización que se realice de la sesión SQL y elaborar un poco las sentencias que utilicemos como fuente de datos.

En este ejemplo vamos a generar un fichero con estructura .csv utilizando la coma como separador y las dobles comillas para las columnas de tipo texto:

SET LINESIZE 80
SET NEWPAGE NONE
SET HEADING OFF
SET COLSEP ''
SET RECSEP OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET TRIMOUT OFF
SET TRIMSPOOL OFF
SET VERIFY OFF

ACCEPT what_owner PROMPT 'what owner? '

spool all_tables_list.csv;

-- Headers
select '"OWNER","TABLE NAME","NUM ROWS"' from dual;
-- Data
select '"'||owner||'","'||table_name||'",'||nvl(num_rows,0)||''
from all_tables
where owner = '&what_owner';

spool off;

Si guardamos el script del cuadro superior en un fichero con nombre «all_tables_list.sql» y lo ejecutamos el resultado mostrará algo similar a las siguientes líneas (el resultado puede variar dependiendo de la versión de Oracle y las opciones que se tengan instaladas):

SQL> @all_tables_list.sql
 what owner? SYS
 "OWNER","TABLE NAME","NUM ROWS"
 "SYS","AUDIT_ACTIONS",0
 "SYS","AW$AWCREATE",0
 "SYS","AW$AWMD",0
 "SYS","AW$EXPRESS",0
 "SYS","DUAL",0
 "SYS","ODCI_SECOBJ$",0
 "SYS","ODCI_WARNINGS$",0
 "SYS","OLAPTABLEVELS",0
 "SYS","OLAPTABLEVELTUPLES",0
 "SYS","PSTUBTBL",0
 "SYS","STMT_AUDIT_OPTION_MAP",0
 "SYS","SYSTEM_PRIVILEGE_MAP",0
 "SYS","TABLE_PRIVILEGE_MAP",0

Mediante el comando «spool», el script guarda el resultado de la consulta en un fichero con nombre «all_tables_list.csv». El fichero se escribirá en la ruta desde la que hayamos ejecutado SQL*Plus.

Si abrimos este fichero con una aplicación para el tratamiento de hojas de cálculo podremos trabajar con los datos y realizar ordenaciones, agrupaciones y todas las operaciones propias de estas aplicaciones.