http://git-wip-us.apache.org/repos/asf/drill-site/blob/c4de0f83/docs/data-type-conversion/index.html
----------------------------------------------------------------------
diff --git a/docs/data-type-conversion/index.html
b/docs/data-type-conversion/index.html
new file mode 100644
index 0000000..8052e8d
--- /dev/null
+++ b/docs/data-type-conversion/index.html
@@ -0,0 +1,1762 @@
+<!DOCTYPE html>
+<html>
+
+<head>
+
+<meta charset="UTF-8">
+<meta name=viewport content="width=device-width, initial-scale=1">
+
+
+<title>Data Type Conversion - Apache Drill</title>
+
+<link href="/css/syntax.css" rel="stylesheet" type="text/css">
+<link href="/css/style.css" rel="stylesheet" type="text/css">
+<link href="/css/arrows.css" rel="stylesheet" type="text/css">
+<link href="/css/breadcrumbs.css" rel="stylesheet" type="text/css">
+<link href="/css/code.css" rel="stylesheet" type="text/css">
+<link rel="stylesheet"
href="//maxcdn.bootstrapcdn.com/font-awesome/4.3.0/css/font-awesome.min.css">
+<link href="/css/responsive.css" rel="stylesheet" type="text/css">
+
+<link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">
+<link rel="icon" href="/favicon.ico" type="image/x-icon">
+
+<script language="javascript" type="text/javascript"
src="/js/lib/jquery-1.11.1.min.js"></script>
+<script language="javascript" type="text/javascript"
src="/js/lib/jquery.easing.1.3.js"></script>
+<script language="javascript" type="text/javascript"
src="/js/modernizr.custom.js"></script>
+<script language="javascript" type="text/javascript"
src="/js/script.js"></script>
+<script language="javascript" type="text/javascript"
src="/js/drill.js"></script>
+
+
+</head>
+
+<body onResize="resized();">
+ <div class="page-wrap">
+ <div class="bui"></div>
+
+<div id="menu" class="mw">
+<ul>
+ <li class='toc-categories'>
+ <a class="expand-toc-icon" href="javascript:void(0);"><i class="fa
fa-bars"></i></a>
+ </li>
+ <li class="logo"><a href="/"></a></li>
+ <li class='expand-menu'>
+ <a href="javascript:void(0);"><span class='menu-text'>Menu</span><span
class='expand-icon'><i class="fa fa-bars"></i></span></a>
+ </li>
+ <li class='clear-float'></li>
+ <li class="documentation-menu">
+ <a href="/docs/">Documentation</a>
+ <ul>
+
+ <li><a href="/docs/getting-started/">Getting Started</a></li>
+
+ <li><a href="/docs/architecture/">Architecture</a></li>
+
+ <li><a href="/docs/tutorials/">Tutorials</a></li>
+
+ <li><a href="/docs/install-drill/">Install Drill</a></li>
+
+ <li><a href="/docs/connect-a-data-source/">Connect a Data
Source</a></li>
+
+ <li><a href="/docs/odbc-jdbc-interfaces/">ODBC/JDBC Interfaces</a></li>
+
+ <li><a href="/docs/query-data/">Query Data</a></li>
+
+ <li><a href="/docs/sql-reference/">SQL Reference</a></li>
+
+ <li><a href="/docs/data-sources-and-file-formats/">Data Sources and
File Formats</a></li>
+
+ <li><a href="/docs/develop-custom-functions/">Develop Custom
Functions</a></li>
+
+ <li><a href="/docs/manage-drill/">Manage Drill</a></li>
+
+ <li><a href="/docs/developer-information/">Developer
Information</a></li>
+
+ <li><a href="/docs/release-notes/">Release Notes</a></li>
+
+ <li><a href="/docs/sample-datasets/">Sample Datasets</a></li>
+
+ <li><a href="/docs/archived-pages/">Archived Pages</a></li>
+
+ <li><a href="/docs/progress-reports/">Progress Reports</a></li>
+
+ <li><a href="/docs/project-bylaws/">Project Bylaws</a></li>
+
+ </ul>
+ </li>
+ <li class='nav'>
+ <a href="/community-resources/">Community</a>
+ <ul>
+ <li><a href="/team/">Team</a></li>
+ <li><a href="/mailinglists/">Mailing Lists</a></li>
+ <li><a href="/community-resources/">Community Resources</a></li>
+ </ul>
+ </li>
+ <li class='nav'><a href="/faq/">FAQ</a></li>
+ <li class='nav'><a href="/blog/">Blog</a></li>
+ <li id="twitter-menu-item"><a href="https://twitter.com/apachedrill";
title="apachedrill on twitter" target="_blank"><img
src="/images/twitter_32_26_white.png" alt="twitter logo" align="center"></a>
</li>
+ <li class='search-bar'>
+ <form id="drill-search-form">
+ <input type="text" placeholder="Search Apache Drill"
id="drill-search-term" />
+ <button type="submit">
+ <i class="fa fa-search"></i>
+ </button>
+ </form>
+ </li>
+ <li class="d">
+ <a href="/download/">
+ <i class="fa fa-cloud-download"></i> Download
+ </a>
+ </li>
+</ul>
+</div>
+
+
+
+
+
+
+
+<aside class="sidebar">
+ <div class="docsidebar">
+ <div class="docsidebarwrapper">
+ <ul style="display: block;">
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Getting
Started</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/drill-introduction/">Drill Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/why-drill/">Why Drill</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript:
void(0);">Architecture</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/architecture-introduction/">Architecture Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/core-modules/">Core Modules</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript:
void(0);">Architectural Highlights</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/flexibility/">Flexibility</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/performance/">Performance</a></li>
+
+ </ul>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript:
void(0);">Tutorials</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/tutorials-introduction/">Tutorials Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/drill-in-10-minutes/">Drill in 10 Minutes</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/analyzing-the-yelp-academic-dataset/">Analyzing the Yelp Academic
Dataset</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Learn
Drill with the MapR Sandbox</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/about-the-mapr-sandbox/">About the MapR Sandbox</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/installing-the-apache-drill-sandbox/">Installing the Apache Drill
Sandbox</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/getting-to-know-the-drill-sandbox/">Getting to Know the Drill
Sandbox</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/lession-1-learn-about-the-data-set/">Lession 1: Learn about the
Data Set</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/lession-2-run-queries-with-ansi-sql/">Lession 2: Run Queries with
ANSI SQL</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/lession-3-run-queries-on-complex-data-types/">Lession 3: Run
Queries on Complex Data Types</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/summary/">Summary</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/analyzing-highly-dynamic-datasets/">Analyzing Highly Dynamic
Datasets</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Install
Drill</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/install-drill-introduction/">Install Drill Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/deploying-drill-in-a-cluster/">Deploying Drill in a Cluster</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Installing
Drill in Embedded Mode</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/embedded-mode-prerequisites/">Embedded Mode Prerequisites</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/installing-drill-on-linux/">Installing Drill on Linux</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/installing-drill-on-mac-os-x/">Installing Drill on Mac OS X</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/installing-drill-on-windows/">Installing Drill on Windows</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/installing-drill-in-distributed-mode/">Installing Drill in
Distributed Mode</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Connect a Data
Source</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/connect-a-data-source-introduction/">Connect a Data Source
Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/storage-plugin-registration/">Storage Plugin Registration</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Storage
Plugin Configuration</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/plugin-configuration-introduction/">Plugin Configuration
Introduction</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/workspaces/">Workspaces</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/file-system-storage-plugin/">File System Storage Plugin</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/hbase-storage-plugin/">HBase Storage Plugin</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/hive-storage-plugin/">Hive Storage Plugin</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/drill-default-input-format/">Drill Default Input Format</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/mongodb-plugin-for-apache-drill/">MongoDB Plugin for Apache
Drill</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/mapr-db-format/">MapR-DB Format</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">ODBC/JDBC
Interfaces</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/interfaces-introduction/">Interfaces Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/using-jdbc/">Using JDBC</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Using ODBC
on Linux and Mac OS X</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/odbc-on-linux-and-mac-introduction/">ODBC on Linux and Mac
Introduction</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/installing-the-driver-on-linux/">Installing the Driver on
Linux</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/installing-the-driver-on-mac-os-x/">Installing the Driver on Mac OS
X</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/configuring-connections-on-linux-and-mac-os-x/">Configuring
Connections on Linux and Mac OS X</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/driver-configuration-options/">Driver Configuration Options</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/using-a-connection-string/">Using a Connection String</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/advanced-properties/">Advanced Properties</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/testing-the-odbc-connection/">Testing the ODBC Connection</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Using ODBC
on Windows</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/installing-the-driver-on-windows/">Installing the Driver on
Windows</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/configuring-connections-on-windows/">Configuring Connections on
Windows</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/connecting-to-odbc-data-sources/">Connecting to ODBC Data
Sources</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/tableau-examples/">Tableau Examples</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/using-drill-explorer-on-windows/">Using Drill Explorer on
Windows</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/using-microstrategy-analytics-with-drill/">Using MicroStrategy
Analytics with Drill</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Query
Data</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/query-data-introduction/">Query Data Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Querying a
File System</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/querying-a-file-system-introduction/">Querying a File System
Introduction</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/querying-json-files/">Querying JSON Files</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/querying-parquet-files/">Querying Parquet Files</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/querying-plain-text-files/">Querying Plain Text Files</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/querying-directories/">Querying Directories</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/querying-hbase/">Querying HBase</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Querying
Complex Data</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/querying-complex-data-introduction/">Querying Complex Data
Introduction</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/sample-data-donuts/">Sample Data: Donuts</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/selecting-flat-data/">Selecting Flat Data</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/using-sql-functions-clauses-and-joins/">Using SQL Functions,
Clauses, and Joins</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/selecting-nested-data-for-a-column/">Selecting Nested Data for a
Column</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/selecting-multiple-columns-within-nested-data/">Selecting Multiple
Columns Within Nested Data</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/querying-hive/">Querying Hive</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/querying-the-information-schema/">Querying the INFORMATION
SCHEMA</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/querying-system-tables/">Querying System Tables</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1 current_section "><a href="javascript:
void(0);">SQL Reference</a></li>
+ <ul class="current_section">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/sql-reference-introduction/">SQL Reference Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Data
Types</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/supported-data-types/">Supported Data Types</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/date-time-and-timestamp/">Date, Time, and Timestamp</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/handling-different-data-types/">Handling Different Data
Types</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/lexical-structure/">Lexical Structure</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/operators/">Operators</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">SQL
Functions</a></li>
+ <ul style="">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/about-sql-function-examples/">About SQL Function Examples</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/math-and-trig/">Math and Trig</a></li>
+
+ <li class="toctree-l3 current"><a class="reference internal"
href="/docs/data-type-conversion/">Data Type Conversion</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/date-time-functions-and-arithmetic/">Date/Time Functions and
Arithmetic</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/string-manipulation/">String Manipulation</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/aggregate-and-aggregate-statistical/">Aggregate and Aggregate
Statistical</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/functions-for-handling-nulls/">Functions for Handling Nulls</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Nested
Data Functions</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/nested-data-limitations/">Nested Data Limitations</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/flatten/">FLATTEN</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/kvgen/">KVGEN</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/repeated-count/">REPEATED_COUNT</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/repeated-contains/">REPEATED_CONTAINS</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/query-directory-functions/">Query Directory Functions</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">SQL
Commands</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/supported-sql-commands/">Supported SQL Commands</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/alter-session-command/">ALTER SESSION Command</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/alter-system-command/">ALTER SYSTEM Command</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/create-table-as-ctas-command/">CREATE TABLE AS (CTAS)
command</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/create-view-command/">CREATE VIEW command</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/describe-command/">DESCRIBE Command</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/explain-commands/">EXPLAIN commands</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/select-statements/">SELECT Statements</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/show-databases-and-show-schemas-command/">SHOW DATABASES AND SHOW
SCHEMAS Command</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/show-files-command/">SHOW FILES Command</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/show-tables-command/">SHOW TABLES Command</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/use-command/">USE Command</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">SQL
Conditional Expressions</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/case/">CASE</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/reserved-keywords/">Reserved Keywords</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/sql-extensions/">SQL Extensions</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Data Sources
and File Formats</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/data-sources-and-file-formats-introduction/">Data Sources and File
Formats Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/hive-to-drill-data-type-mapping/">Hive-to-Drill Data Type
Mapping</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/deploying-and-using-a-hive-udf/">Deploying and Using a Hive
UDF</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/parquet-format/">Parquet Format</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/json-data-model/">JSON Data Model</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Develop Custom
Functions</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/develop-custom-functions-introduction/">Develop Custom Functions
Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/develop-a-simple-function/">Develop a Simple Function</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/developing-an-aggregate-function/">Developing an Aggregate
Function</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/adding-custom-functions-to-drill/">Adding Custom Functions to
Drill</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/using-custom-functions-in-queries/">Using Custom Functions in
Queries</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/custom-function-interfaces/">Custom Function Interfaces</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Manage
Drill</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/manage-drill-introduction/">Manage Drill Introduction</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/configuring-drill-in-a-dedicated-cluster/">Configuring Drill in a
Dedicated Cluster</a></li>
+
+
+
+ <li class="toctree-l2"><a href="javascript:
void(0);">Configuring a Multitenant Cluster</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/configuring-a-multitenant-cluster-introduction/">Configuring a
Multitenant Cluster Introduction</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/configuring-multitenant-resources/">Configuring Multitenant
Resources</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/configuring-resources-for-a-shared-drillbit/">Configuring Resources
for a Shared Drillbit</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a href="javascript:
void(0);">Configuration Options</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/configuration-options-introduction/">Configuration Options
Introduction</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/start-up-options/">Start-Up Options</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/planning-and-execution-options/">Planning and Execution
Options</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/persistent-configuration-storage/">Persistent Configuration
Storage</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/starting-stopping-drill/">Starting/Stopping Drill</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/ports-used-by-drill/">Ports Used by Drill</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/partition-pruning/">Partition Pruning</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/monitoring-and-canceling-queries-in-the-drill-web-ui/">Monitoring
and Canceling Queries in the Drill Web UI</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Developer
Information</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Develop
Drill</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/compiling-drill-from-source/">Compiling Drill from Source</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/drill-patch-review-tool/">Drill Patch Review Tool</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Contribute
to Drill</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/apache-drill-contribution-guidelines/">Apache Drill Contribution
Guidelines</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/apache-drill-contribution-ideas/">Apache Drill Contribution
Ideas</a></li>
+
+ </ul>
+
+
+
+ <li class="toctree-l2"><a href="javascript: void(0);">Design
Docs</a></li>
+ <ul style="display: none">
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/drill-plan-syntax/">Drill Plan Syntax</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/rpc-overview/">RPC Overview</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/query-stages/">Query Stages</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/useful-research/">Useful Research</a></li>
+
+ <li class="toctree-l3"><a class="reference internal"
href="/docs/value-vectors/">Value Vectors</a></li>
+
+ </ul>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Release
Notes</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/apache-drill-0-5-0-release-notes/">Apache Drill 0.5.0 Release
Notes</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/apache-drill-0-4-0-release-notes/">Apache Drill 0.4.0 Release
Notes</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/apache-drill-m1-release-notes-apache-drill-alpha/">Apache Drill M1
Release Notes (Apache Drill Alpha)</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/apache-drill-m1-release-notes-apache-drill-alpha/">Apache Drill M1
Release Notes (Apache Drill Alpha)</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/apache-drill-0-6-0-release-notes/">Apache Drill 0.6.0 Release
Notes</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/apache-drill-0-7-0-release-notes/">Apache Drill 0.7.0 Release
Notes</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/apache-drill-0-8-0-release-notes/">Apache Drill 0.8.0 Release
Notes</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Sample
Datasets</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/aol-search/">AOL Search</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/enron-emails/">Enron Emails</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/wikipedia-edit-history/">Wikipedia Edit History</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Archived
Pages</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/how-to-run-the-drill-demo/">How to Run the Drill Demo</a></li>
+
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/what-is-apache-drill/">What is Apache Drill</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a href="javascript: void(0);">Progress
Reports</a></li>
+ <ul style="display: none">
+
+
+ <li class="toctree-l2"><a class="reference internal"
href="/docs/2014-q1-drill-report/">2014 Q1 Drill Report</a></li>
+
+
+ </ul>
+
+
+
+ <li class="toctree-l1"><a class="reference internal"
href="/docs/project-bylaws/">Project Bylaws</a></li>
+
+
+ </ul>
+
+ </div>
+ </div>
+</aside>
+
+
+ <nav class="breadcrumbs">
+ <li><a href="/docs/">Docs</a></li>
+
+
+ <li><a href="/docs/sql-reference/">SQL Reference</a></li>
+
+ <li><a href="/docs/sql-functions/">SQL Functions</a></li>
+
+ <li>Data Type Conversion</li>
+</nav>
+
+ <div class="main-content-wrapper">
+ <div class="main-content">
+
+
+ <a class="edit-link"
href="https://github.com/apache/drill/blob/gh-pages/_docs/sql-reference/sql-functions/020-data-type-conversion.md";
target="_blank"><i class="fa fa-pencil-square-o"></i></a>
+
+
+ <div class="int_title">
+ <h1>Data Type Conversion</h1>
+
+ </div>
+
+ <link href="/css/docpage.css" rel="stylesheet" type="text/css">
+
+ <div class="int_text" align="left">
+
+ <p>Drill supports the following functions for casting and converting
data types:</p>
+
+<ul>
+<li><a href="/docs/data-type-conversion#cast">CAST</a></li>
+<li><a
href="/docs/data-type-conversion#convert_to-and-convert_from">CONVERT_TO and
CONVERT_FROM</a></li>
+<li><a href="/docs/data-type-conversion#other-data-type-conversions">Other
Data Type Conversions</a></li>
+</ul>
+
+<h2 id="cast">CAST</h2>
+
+<p>The CAST function converts an entity, such as an expression that evaluates
to a single value, from one type to another.</p>
+
+<h3 id="cast-syntax">CAST Syntax</h3>
+<div class="highlight"><pre><code class="language-text" data-lang="text">CAST
(<expression> AS <data type>)
+</code></pre></div>
+<p><em>expression</em></p>
+
+<p>A combination of one or more values, operators, and SQL functions that
evaluate to a value</p>
+
+<p><em>data type</em></p>
+
+<p>The target data type, such as INTEGER or DATE, to which to cast the
expression</p>
+
+<h3 id="cast-usage-notes">CAST Usage Notes</h3>
+
+<p>If the SELECT statement includes a WHERE clause that compares a column of
an unknown data type, cast both the value of the column and the comparison
value in the WHERE clause. For example:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT c_row, CAST(c_int AS DECIMAL(28,8)) FROM mydata WHERE
CAST(c_int AS DECIMAL(28,8)) > -3.0;
+</code></pre></div>
+<p>Use CONVERT_TO and CONVERT_FROM instead of the CAST function for converting
binary data types with one exception: When converting an INT or BIGINT number,
having a byte count in the destination/source that does not match the byte
count of the number in the VARBINARY source/destination, use CAST. </p>
+
+<p>Refer to the following tables for information about the data types to use
for casting:</p>
+
+<ul>
+<li><a href="/docs/supported-data-types-for-casting">Supported Data Types for
Casting</a></li>
+<li><a href="/docs/explicit-type-casting-maps">Explicit Type Casting
Maps</a></li>
+</ul>
+
+<h2 id="data-type-conversion-examples">Data Type Conversion Examples</h2>
+
+<p>The following examples show how to cast a string to a number, a number to a
string, and one type of number to another.</p>
+
+<h3 id="casting-a-character-string-to-a-number">Casting a Character String to
a Number</h3>
+
+<p>You cannot cast a character string that includes a decimal point to an INT
or BIGINT. For example, if you have "1200.50" in a JSON file,
attempting to select and cast the string to an INT fails. As a workaround, cast
to a FLOAT or DECIMAL type, and then to an INT. </p>
+
+<p>The following example shows how to cast a character to a DECIMAL having two
decimal places.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT CAST('1' as DECIMAL(28, 2)) FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 1.00 |
++------------+
+</code></pre></div>
+<h3 id="casting-a-number-to-a-character-string">Casting a Number to a
Character String</h3>
+
+<p>The first example shows Drill casting a number to a VARCHAR having a length
of 3 bytes: The result is a 3-character string, 456. Drill supports the CHAR
and CHARACTER VARYING alias.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT CAST(456 as VARCHAR(3)) FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 456 |
++------------+
+1 row selected (0.08 seconds)
+
+SELECT CAST(456 as CHAR(3)) FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 456 |
++------------+
+1 row selected (0.093 seconds)
+</code></pre></div>
+<h3 id="casting-from-one-type-of-number-to-another">Casting from One Type of
Number to Another</h3>
+
+<p>Cast an integer to a decimal.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT CAST(-2147483648 AS DECIMAL(28,8)) FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| -2.147483648E9 |
++------------+
+1 row selected (0.08 seconds)
+</code></pre></div>
+<h3 id="casting-intervals">Casting Intervals</h3>
+
+<p>To cast interval data to the INTERVALDAY or INTERVALYEAR types use the
following syntax:</p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">CAST
(column_name AS INTERVAL DAY)
+CAST (column_name AS INTERVAL YEAR)
+</code></pre></div>
+<p>For example, a JSON file named intervals.json contains the following
objects:</p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">{
"INTERVALYEAR_col":"P1Y",
"INTERVALDAY_col":"P1D",
"INTERVAL_col":"P1Y1M1DT1H1M" }
+{ "INTERVALYEAR_col":"P2Y",
"INTERVALDAY_col":"P2D",
"INTERVAL_col":"P2Y2M2DT2H2M" }
+{ "INTERVALYEAR_col":"P3Y",
"INTERVALDAY_col":"P3D",
"INTERVAL_col":"P3Y3M3DT3H3M" }
+</code></pre></div>
+<ol>
+<li><p>Set the storage format to Parquet.</p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">ALTER
SESSION SET `store.format` = 'parquet';
+
++------------+------------+
+| ok | summary |
++------------+------------+
+| true | store.format updated. |
++------------+------------+
+1 row selected (0.037 seconds)
+</code></pre></div></li>
+<li><p>Use a CTAS statement to cast text from a JSON file to year and day
intervals and to write the data to a Parquet table:</p>
+
+<p>CREATE TABLE dfs.tmp.parquet_intervals AS
+(SELECT CAST( INTERVALYEAR_col as interval year) INTERVALYEAR_col,
+ CAST( INTERVALDAY_col as interval day) INTERVALDAY_col
+FROM dfs.<code>/Users/drill/intervals.json</code>);</p></li>
+</ol>
+
+<h2 id="convert_to-and-convert_from">CONVERT_TO and CONVERT_FROM</h2>
+
+<p>The CONVERT_TO and CONVERT_FROM functions encode and decode
+data to and from another data type.</p>
+
+<h3 id="convert_to-and-convert_from-syntax">CONVERT_TO and CONVERT_FROM
Syntax</h3>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">CONVERT_TO (column, type)
+
+CONVERT_FROM(column, type)
+</code></pre></div>
+<p><em>column</em> is the name of a column Drill reads.</p>
+
+<p><em>type</em> is one of the data types listed in the <a
href="/docs/data-types#convert_to-and-convert_from-data-types">CONVERT_TO/FROM
Data Types</a> table.</p>
+
+<h3 id="convert_to-and-convert_from-usage-notes">CONVERT_TO and CONVERT_FROM
Usage Notes</h3>
+
+<p>CONVERT_FROM and CONVERT_TO methods transform a known binary
representation/encoding to a Drill internal format. Use CONVERT_TO and
CONVERT_FROM instead of the CAST function for converting binary data types with
one exception: When converting data represented as a string in HBase to an INT
or BIGINT number, use CAST. CONVERT_TO/FROM functions work for data in a binary
representation and are more efficient to use than CAST. For example, HBase
stores
+data as encoded VARBINARY data. To read HBase data in Drill, convert every
column of an HBase table <em>from</em> binary to an Drill internal type. To
write HBase or Parquet binary data, convert SQL data <em>to</em> binary data
and store the data in an HBase or Parquet while creating a table as a selection
(CTAS).</p>
+
+<p>CONVERT_TO also converts an SQL data type to complex types, including HBase
byte arrays, JSON and Parquet arrays, and maps. CONVERT_FROM converts from
complex types, including HBase arrays, JSON and Parquet arrays and maps to an
SQL data type. </p>
+
+<h3 id="conversion-of-data-types-examples">Conversion of Data Types
Examples</h3>
+
+<p>This example shows how to use the CONVERT_FROM function to convert complex
HBase data to a readable type. The example summarizes and continues the <a
href="/docs/querying-hbase">"Query HBase"</a> example. The <a
href="/docs/querying-hbase">"Query HBase"</a> example stores the
following data in the students table on the Drill Sandbox: </p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">USE
maprdb;
+
+SELECT * FROM students;
+
++------------+------------+------------+
+| row_key | account | address |
++------------+------------+------------+
+| [B@e6d9eb7 | {"name":"QWxpY2U="} |
{"state":"Q0E=","street":"MTIzIEJhbGxtZXIgQXY=","zipcode":"MTIzNDU="}
|
+| [B@2823a2b4 | {"name":"Qm9i"} |
{"state":"Q0E=","street":"MSBJbmZpbml0ZSBMb29w","zipcode":"MTIzNDU="}
|
+| [B@3b8eec02 | {"name":"RnJhbms="} |
{"state":"Q0E=","street":"NDM1IFdhbGtlciBDdA==","zipcode":"MTIzNDU="}
|
+| [B@242895da | {"name":"TWFyeQ=="} |
{"state":"Q0E=","street":"NTYgU291dGhlcm4gUGt3eQ==","zipcode":"MTIzNDU="}
|
++------------+------------+------------+
+4 rows selected (1.335 seconds)
+</code></pre></div>
+<p>You use the CONVERT_FROM function to decode the binary data to render it
readable, selecting a data type to use from the <a
href="/docs/data-type-conversion/#convert_to-and-convert_from-data-types">list
of supported types</a>. JSON supports strings. To convert binary to strings,
use the UTF8 type.:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT CONVERT_FROM(row_key, 'UTF8') AS studentid,
+ CONVERT_FROM(students.account.name, 'UTF8') AS name,
+ CONVERT_FROM(students.address.state, 'UTF8') AS state,
+ CONVERT_FROM(students.address.street, 'UTF8') AS street,
+ CONVERT_FROM(students.address.zipcode, 'UTF8') AS zipcode FROM
students;
+
++------------+------------+------------+------------+------------+
+| studentid | name | state | street | zipcode |
++------------+------------+------------+------------+------------+
+| student1 | Alice | CA | 123 Ballmer Av | 12345 |
+| student2 | Bob | CA | 1 Infinite Loop | 12345 |
+| student3 | Frank | CA | 435 Walker Ct | 12345 |
+| student4 | Mary | CA | 56 Southern Pkwy | 12345 |
++------------+------------+------------+------------+------------+
+4 rows selected (0.504 seconds)
+</code></pre></div>
+<p>This example converts from VARCHAR to a JSON map:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT CONVERT_FROM('{x:100, y:215.6}'
,'JSON') AS MYCOL FROM sys.version;
++------------+
+| MYCOL |
++------------+
+| {"x":100,"y":215.6} |
++------------+
+1 row selected (0.073 seconds)
+</code></pre></div>
+<p>This example uses a list of BIGINT as input and returns a repeated list of
vectors:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT CONVERT_FROM('[ [1, 2], [3, 4], [5]]'
,'JSON') AS MYCOL1 FROM sys.version;
++------------+
+| mycol1 |
++------------+
+| [[1,2],[3,4],[5]] |
++------------+
+1 row selected (0.054 seconds)
+</code></pre></div>
+<p>This example uses a map as input to return a repeated list vector
(JSON).</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT CONVERT_FROM('[{a : 100, b: 200}, {a:300, b:
400}]' ,'JSON') AS MYCOL1 FROM sys.version;
++------------+
+| MYCOL1 |
++------------+
+|
[{"a":100,"b":200},{"a":300,"b":400}] |
++------------+
+1 row selected (0.074 seconds)
+</code></pre></div>
+<h3 id="set-up-a-storage-plugin-for-working-with-hbase-files">Set Up a Storage
Plugin for Working with HBase Files</h3>
+
+<p>This example assumes you are working in the Drill Sandbox. The
<code>maprdb</code> storage plugin definition is limited, so you modify the
<code>dfs</code> storage plugin slightly and use that plugin for this
example.</p>
+
+<ol>
+<li><p>Copy/paste the <code>dfs</code> storage plugin definition to a newly
created plugin called myplugin.</p></li>
+<li><p>Change the root location to "/mapr/demo.mapr.com/tables".
This change allows you to query tables for reading in the tables directory by
workspace.table name. This change allows you to read a table in the
<code>tables</code> directory. You can write a converted version of the table
in the <code>tmp</code> directory because the writable property is true.</p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">{
+ "type": "file",
+ "enabled": true,
+ "connection": "maprfs:///",
+ "workspaces": {
+ "root": {
+ "location": "/mapr/demo.mapr.com/tables",
+ "writable": true,
+ "defaultInputFormat": null
+ },
+
+ . . .
+
+ "tmp": {
+ "location": "/tmp",
+ "writable": true,
+ "defaultInputFormat": null
+ }
+
+ . . .
+
+ "formats": {
+ . . .
+ "maprdb": {
+ "type": "maprdb"
+ }
+ }
+}
+</code></pre></div></li>
+</ol>
+
+<h3 id="convert-the-binary-hbase-students-table-to-json-data">Convert the
Binary HBase Students Table to JSON Data</h3>
+
+<p>First, you set the storage format to JSON. Next, you use the CREATE TABLE
AS SELECT (CTAS) statement to convert from a selected file of a different
format, HBase in this example, to the storage format. You then convert the JSON
file to Parquet using a similar procedure. Set the storage format to Parquet,
and use a CTAS statement to convert to Parquet from JSON. In each case, you <a
href="/docs/data-type-conversion/#convert_to-and-convert_from-data-types">select
UTF8</a> as the file format because the data you are converting from and then
to consists of strings.</p>
+
+<ol>
+<li><p>Start Drill on the Drill Sandbox and set the default storage format
from Parquet to JSON.</p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">ALTER
SESSION SET `store.format`='json';
+</code></pre></div></li>
+<li><p>Use CONVERT_FROM queries to convert the binary data in the HBase
students table to JSON, and store the JSON data in a file. You select a data
type to use from the supported. JSON supports strings. To convert binary to
strings, use the UTF8 type.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">CREATE TABLE tmp.`to_json` AS SELECT
+ CONVERT_FROM(row_key, 'UTF8') AS `studentid`,
+ CONVERT_FROM(students.account.name, 'UTF8') AS name,
+ CONVERT_FROM(students.address.state, 'UTF8') AS state,
+ CONVERT_FROM(students.address.street, 'UTF8') AS street,
+ CONVERT_FROM(students.address.zipcode, 'UTF8') AS zipcode
+FROM root.`students`;
+
++------------+---------------------------+
+| Fragment | Number of records written |
++------------+---------------------------+
+| 0_0 | 4 |
++------------+---------------------------+
+1 row selected (0.41 seconds)
+</code></pre></div></li>
+<li><p>Navigate to the output. </p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">cd
/mapr/demo.mapr.com/tmp/to_json
+ls
+</code></pre></div>
+<p>Output is:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">0_0_0.json
+</code></pre></div></li>
+<li><p>Take a look at the output of <code>to_json</code>:</p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">{
+ "studentid" : "student1",
+ "name" : "Alice",
+ "state" : "CA",
+ "street" : "123 Ballmer Av",
+ "zipcode" : "12345"
+} {
+ "studentid" : "student2",
+ "name" : "Bob",
+ "state" : "CA",
+ "street" : "1 Infinite Loop",
+ "zipcode" : "12345"
+} {
+ "studentid" : "student3",
+ "name" : "Frank",
+ "state" : "CA",
+ "street" : "435 Walker Ct",
+ "zipcode" : "12345"
+} {
+ "studentid" : "student4",
+ "name" : "Mary",
+ "state" : "CA",
+ "street" : "56 Southern Pkwy",
+ "zipcode" : "12345"
+}
+</code></pre></div></li>
+<li><p>Set up Drill to store data in Parquet format.</p>
+<div class="highlight"><pre><code class="language-text" data-lang="text">ALTER
SESSION SET `store.format`='parquet';
++------------+------------+
+| ok | summary |
++------------+------------+
+| true | store.format updated. |
++------------+------------+
+1 row selected (0.056 seconds)
+</code></pre></div></li>
+<li><p>Use CONVERT_TO to convert the JSON data to a binary format in the
Parquet file.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">CREATE TABLE tmp.`json2parquet` AS SELECT
+ CONVERT_TO(studentid, 'UTF8') AS id,
+ CONVERT_TO(name, 'UTF8') AS name,
+ CONVERT_TO(state, 'UTF8') AS state,
+ CONVERT_TO(street, 'UTF8') AS street,
+ CONVERT_TO(zipcode, 'UTF8') AS zip
+FROM tmp.`to_json`;
+
++------------+---------------------------+
+| Fragment | Number of records written |
++------------+---------------------------+
+| 0_0 | 4 |
++------------+---------------------------+
+1 row selected (0.414 seconds)
+</code></pre></div></li>
+<li><p>Take a look at the binary Parquet output:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT * FROM tmp.`json2parquet`;
++------------+------------+------------+------------+------------+
+| id | name | state | street | zip |
++------------+------------+------------+------------+------------+
+| [B@224388b2 | [B@7fc36fb0 | [B@77d9cd57 | [B@7c384839 | [B@530dd5e5 |
+| [B@3155d7fc | [B@7ad6fab1 | [B@37e4b978 | [B@94c91f3 | [B@201ed4a |
+| [B@4fb2c078 | [B@607a2f28 | [B@75ae1c93 | [B@79d63340 | [B@5dbeed3d |
+| [B@2fcfec74 | [B@7baccc31 | [B@d91e466 | [B@6529eb7f | [B@232412bc |
++------------+------------+------------+------------+------------+
+4 rows selected (0.12 seconds)
+</code></pre></div></li>
+<li><p>Use CONVERT_FROM to convert the Parquet data to a readable format:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT CONVERT_FROM(id, 'UTF8') AS id,
+ CONVERT_FROM(name, 'UTF8') AS name,
+ CONVERT_FROM(state, 'UTF8') AS state,
+ CONVERT_FROM(street, 'UTF8') AS address,
+ CONVERT_FROM(zip, 'UTF8') AS zip
+FROM tmp.`json2parquet2`;
+
++------------+------------+------------+------------+------------+
+| id | name | state | address | zip |
++------------+------------+------------+------------+------------+
+| student1 | Alice | CA | 123 Ballmer Av | 12345 |
+| student2 | Bob | CA | 1 Infinite Loop | 12345 |
+| student3 | Frank | CA | 435 Walker Ct | 12345 |
+| student4 | Mary | CA | 56 Southern Pkwy | 12345 |
++------------+------------+------------+------------+------------+
+4 rows selected (0.182 seconds)
+</code></pre></div></li>
+</ol>
+
+<h2 id="other-data-type-conversions">Other Data Type Conversions</h2>
+
+<p>Drill supports the format for date and time literals shown in the following
examples:</p>
+
+<ul>
+<li><p>2008-12-15</p></li>
+<li><p>22:55:55.123...</p></li>
+</ul>
+
+<p>If you have dates and times in other formats, use a data type conversion
function to perform the following conversions:</p>
+
+<ul>
+<li>A TIMESTAMP, DATE, TIME, INTEGER, FLOAT, or DOUBLE to a character string,
which is of type VARCHAR</li>
+<li>A character string to a DATE</li>
+<li>A character string to a NUMBER</li>
+</ul>
+
+<p>The following table lists data type formatting functions that you can
+use in your Drill queries as described in this section:</p>
+
+<table><thead>
+<tr>
+<th><strong>Function</strong></th>
+<th><strong>Return Type</strong></th>
+</tr>
+</thead><tbody>
+<tr>
+<td><a href="#TO_CHAR">TO_CHAR</a>(expression, format)</td>
+<td>VARCHAR</td>
+</tr>
+<tr>
+<td><a href="#TO_DATE">TO_DATE</a>(expression, format)</td>
+<td>DATE</td>
+</tr>
+<tr>
+<td><a href="#TO_NUMBER">TO_NUMBER</a>(VARCHAR, format)</td>
+<td>DECIMAL</td>
+</tr>
+<tr>
+<td><a href="#TO_TIMESTAMP">TO_TIMESTAMP</a>(VARCHAR, format)</td>
+<td>TIMESTAMP</td>
+</tr>
+<tr>
+<td><a href="#TO_TIMESTAMP">TO_TIMESTAMP</a>(DOUBLE)</td>
+<td>TIMESTAMP</td>
+</tr>
+</tbody></table>
+
+<h3 id="format-specifiers-for-numerical-conversions">Format Specifiers for
Numerical Conversions</h3>
+
+<p>Use the following format specifiers for converting numbers:
+<table >
+ <tr >
+ <th align=left>Symbol
+ <th align=left>Location
+ <th align=left>Meaning
+ <tr valign=top>
+ <td><code>0</code>
+ <td>Number
+ <td>Digit
+ <tr >
+ <td><code>#</code>
+ <td>Number
+ <td>Digit, zero shows as absent
+ <tr valign=top>
+ <td><code>.</code>
+ <td>Number
+ <td>Decimal separator or monetary decimal separator
+ <tr >
+ <td><code>-</code>
+ <td>Number
+ <td>Minus sign
+ <tr valign=top>
+ <td><code>,</code>
+ <td>Number
+ <td>Grouping separator
+ <tr >
+ <td><code>E</code>
+ <td>Number
+ <td>Separates mantissa and exponent in scientific notation.
+ <em>Need not be quoted in prefix or suffix.</em>
+ <tr valign=top>
+ <td><code>;</code>
+ <td>Subpattern boundary
+ <td>Separates positive and negative subpatterns
+ <tr >
+ <td><code>%</code>
+ <td>Prefix or suffix
+ <td>Multiply by 100 and show as percentage
+ <tr valign=top>
+ <td><code>\u2030</code>
+ <td>Prefix or suffix
+ <td>Multiply by 1000 and show as per mille value
+ <tr >
+ <td><code>¤</code> (<code>\u00A4</code>)
+ <td>Prefix or suffix
+ <td>Currency sign, replaced by currency symbol. If
+ doubled, replaced by international currency symbol.
+ If present in a pattern, the monetary decimal separator
+ is used instead of the decimal separator.
+ <tr valign=top>
+ <td><code>'</code>
+ <td>Prefix or suffix
+ <td>Used to quote special characters in a prefix or suffix,
+ for example, <code>"'#'#"</code> formats 123 to
+ <code>"#123"</code>. To create a single quote
+ itself, use two in a row: <code>"#
o''clock"</code>.
+ </table></p>
+
+<h3 id="format-specifiers-for-date/time-conversions">Format Specifiers for
Date/Time Conversions</h3>
+
+<p>Use the following format specifiers for date/time conversions:</p>
+
+<table>
+ <tr>
+ <th>Symbol</th>
+ <th>Meaning</th>
+ <th>Presentation</th>
+ <th>Examples</th>
+ </tr>
+ <tr>
+ <td>G</td>
+ <td>era</td>
+ <td>text</td>
+ <td>AD</td>
+ </tr>
+ <tr>
+ <td>C</td>
+ <td>century of era (>=0)</td>
+ <td>number</td>
+ <td>20</td>
+ </tr>
+ <tr>
+ <td>Y</td>
+ <td>year of era (>=0)</td>
+ <td>year</td>
+ <td>1996</td>
+ </tr>
+ <tr>
+ <td>x</td>
+ <td>weekyear</td>
+ <td>year</td>
+ <td>1996</td>
+ </tr>
+ <tr>
+ <td>w</td>
+ <td>week of weekyear</td>
+ <td>number</td>
+ <td>27</td>
+ </tr>
+ <tr>
+ <td>e</td>
+ <td>day of week</td>
+ <td>number</td>
+ <td>2</td>
+ </tr>
+ <tr>
+ <td>E</td>
+ <td>day of week</td>
+ <td>text</td>
+ <td>Tuesday; Tue</td>
+ </tr>
+ <tr>
+ <td>y</td>
+ <td>year</td>
+ <td>year</td>
+ <td>1996</td>
+ </tr>
+ <tr>
+ <td>D</td>
+ <td>day of year</td>
+ <td>number</td>
+ <td>189</td>
+ </tr>
+ <tr>
+ <td>M</td>
+ <td>month of year</td>
+ <td>month</td>
+ <td>July; Jul; 07</td>
+ </tr>
+ <tr>
+ <td>d</td>
+ <td>day of month</td>
+ <td>number</td>
+ <td>10</td>
+ </tr>
+ <tr>
+ <td>a</td>
+ <td>halfday of day</td>
+ <td>text</td>
+ <td>PM</td>
+ </tr>
+ <tr>
+ <td>K</td>
+ <td>hour of halfday (0~11)</td>
+ <td>number</td>
+ <td>0</td>
+ </tr>
+ <tr>
+ <td>h</td>
+ <td>clockhour of halfday (1~12)number</td>
+ <td>12</td>
+ <td></td>
+ </tr>
+ <tr>
+ <td>H</td>
+ <td>hour of day (0~23)</td>
+ <td>number</td>
+ <td>0</td>
+ </tr>
+ <tr>
+ <td>k</td>
+ <td>clockhour of day (1~24)</td>
+ <td>number</td>
+ <td>24</td>
+ </tr>
+ <tr>
+ <td>m</td>
+ <td>minute of hour</td>
+ <td>number</td>
+ <td>30</td>
+ </tr>
+ <tr>
+ <td>s</td>
+ <td>second of minute</td>
+ <td>number</td>
+ <td>55</td>
+ </tr>
+ <tr>
+ <td>S</td>
+ <td>fraction of second</td>
+ <td>number</td>
+ <td>978</td>
+ </tr>
+ <tr>
+ <td>z</td>
+ <td>time zone</td>
+ <td>text</td>
+ <td>Pacific Standard Time; PST</td>
+ </tr>
+ <tr>
+ <td>Z</td>
+ <td>time zone offset/id</td>
+ <td>zone</td>
+ <td>-0800; -08:00; America/Los_Angeles</td>
+ </tr>
+ <tr>
+ <td>'</td>
+ <td>single quotation mark, escape for text delimiter</td>
+ <td>literal</td>
+ <td></td>
+ </tr>
+</table>
+
+<p>For more information about specifying a format, refer to one of the
following format specifier documents:</p>
+
+<ul>
+<li><a
href="http://docs.oracle.com/javase/7/docs/api/java/text/DecimalFormat.html";>Java
DecimalFormat class</a> format specifiers </li>
+<li><a
href="http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html";>Java
DateTimeFormat class</a> format specifiers</li>
+</ul>
+
+<h2 id="to_char">TO_CHAR</h2>
+
+<p>TO_CHAR converts a number, date, time, or timestamp expression to a
character string.</p>
+
+<h3 id="to_char-syntax">TO_CHAR Syntax</h3>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">TO_CHAR (expression, 'format')
+</code></pre></div>
+<p><em>expression</em> is a INTEGER, FLOAT, DOUBLE, DATE, TIME, or TIMESTAMP
expression. </p>
+
+<p><em>'format'</em> is a format specifier enclosed in single
quotation marks that sets a pattern for the output formatting. </p>
+
+<h3 id="to_char-usage-notes">TO_CHAR Usage Notes</h3>
+
+<p>You can use the âzâ option to identify the time zone in TO_TIMESTAMP to
make sure the timestamp has the timezone in it, as shown in the TO_TIMESTAMP
description.</p>
+
+<h3 id="to_char-examples">TO_CHAR Examples</h3>
+
+<p>Convert a FLOAT to a character string.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_CHAR(125.789383, '#,###.###') FROM
sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 125.789 |
++------------+
+</code></pre></div>
+<p>Convert an integer to a character string.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_CHAR(125, '#,###.###') FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 125 |
++------------+
+1 row selected (0.083 seconds)
+</code></pre></div>
+<p>Convert a date to a character string.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_CHAR((CAST('2008-2-23' AS DATE)),
'yyyy-MMM-dd') FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 2008-Feb-23 |
++------------+
+</code></pre></div>
+<p>Convert a time to a string.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_CHAR(CAST('12:20:30' AS TIME), 'HH mm
ss') FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 12 20 30 |
++------------+
+1 row selected (0.07 seconds)
+</code></pre></div>
+<p>Convert a timestamp to a string.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_CHAR(CAST('2015-2-23 12:00:00' AS
TIMESTAMP), 'yyyy MMM dd HH:mm:ss') FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 2015 Feb 23 12:00:00 |
++------------+
+1 row selected (0.075 seconds)
+</code></pre></div>
+<h2 id="to_date">TO_DATE</h2>
+
+<p>Converts a character string or a UNIX epoch timestamp to a date.</p>
+
+<h3 id="to_date-syntax">TO_DATE Syntax</h3>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">TO_DATE (expression [, 'format'])
+</code></pre></div>
+<p><em>expression</em> is a character string enclosed in single quotation
marks or a Unix epoch timestamp in milliseconds, not enclosed in single
quotation marks. </p>
+
+<p><em>'format'</em> is a format specifier enclosed in single
quotation marks that sets a pattern for the output formatting. Use this option
only when the expression is a character string, not a UNIX epoch timestamp. </p>
+
+<h3 id="to_date-usage-notes">TO_DATE Usage Notes</h3>
+
+<p>Specify a format using patterns defined in <a
href="http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html";>Java
DateTimeFormat class</a>. The TO_TIMESTAMP function takes a Unix epoch
timestamp. The TO_DATE function takes a UNIX epoch timestamp in
milliseconds.</p>
+
+<p>To compare dates in the WHERE clause, use TO_DATE on the value in the date
column and in the comparison value. For example:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT <fields> FROM <plugin> WHERE
TO_DATE(<field>, <format>) < TO_DATE (<value>,
<format>);
+</code></pre></div>
+<p>For example:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_DATE(`date`, 'yyyy-MM-dd') FROM
`sample.json`;
+
++------------+
+| EXPR$0 |
++------------+
+| 2013-07-26 |
+| 2013-05-16 |
+| 2013-06-09 |
+| 2013-07-19 |
+| 2013-07-21 |
++------------+
+5 rows selected (0.134 seconds)
+
+SELECT TO_DATE(`date`, 'yyyy-MM-dd') FROM `sample.json` WHERE
TO_DATE(`date`, 'yyyy-MM-dd') < TO_DATE('2013-07-20',
'yyyy-MM-dd');
+
++------------+
+| EXPR$0 |
++------------+
+| 2013-05-16 |
+| 2013-06-09 |
+| 2013-07-19 |
++------------+
+3 rows selected (0.177 seconds)
+</code></pre></div>
+<h3 id="to_date-examples">TO_DATE Examples</h3>
+
+<p>The first example converts a character string to a date. The second example
extracts the year to verify that Drill recognizes the date as a date type. </p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_DATE('2015-FEB-23', 'yyyy-MMM-dd')
FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 2015-02-23 |
++------------+
+1 row selected (0.077 seconds)
+
+SELECT EXTRACT(year from mydate) `extracted year` FROM (SELECT
TO_DATE('2015-FEB-23', 'yyyy-MMM-dd') AS mydate FROM
sys.version);
+
++------------+
+| myyear |
++------------+
+| 2015 |
++------------+
+1 row selected (0.128 seconds)
+</code></pre></div>
+<p>The following example converts a UNIX epoch timestamp to a date.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_DATE(1427849046000) FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 2015-04-01 |
++------------+
+1 row selected (0.082 seconds)
+</code></pre></div>
+<h2 id="to_number">TO_NUMBER</h2>
+
+<p>TO_NUMBER converts a character string to a formatted number using a format
specification.</p>
+
+<h3 id="syntax">Syntax</h3>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">TO_NUMBER ('string', 'format')
+</code></pre></div>
+<p><em>'string'</em> is a character string enclosed in single
quotation marks. </p>
+
+<p><em>'format'</em> is one or more <a
href="http://docs.oracle.com/javase/7/docs/api/java/text/DecimalFormat.html";>Java
DecimalFormat class</a> specifiers enclosed in single quotation marks that set
a pattern for the output formatting.</p>
+
+<h3 id="to_number-usage-notes">TO_NUMBER Usage Notes</h3>
+
+<p>The data type of the output of TO_NUMBER is a numeric. You can use the
following <a
href="http://docs.oracle.com/javase/7/docs/api/java/text/DecimalFormat.html";>Java
DecimalFormat class</a> specifiers to set the output formatting. </p>
+
+<ul>
+<li><p>Digit placeholder. </p></li>
+<li><p>0<br>
+Digit placeholder. If a value has a digit in the position where the zero
'0' appears in the format string, that digit appears in the output;
otherwise, a '0' appears in that position in the output.</p></li>
+<li><p>.<br>
+Decimal point. Make the first '.' character in the format string the
location of the decimal separator in the value; ignore any additional
'.' characters.</p></li>
+<li><p>,<br>
+Comma grouping separator. </p></li>
+<li><p>E
+Exponent. Separates mantissa and exponent in scientific notation. </p></li>
+</ul>
+
+<h3 id="to_number-examples">TO_NUMBER Examples</h3>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_NUMBER('987,966', '######') FROM
sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 987.0 |
++------------+
+
+SELECT TO_NUMBER('987.966', '###.###') FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 987.966 |
++------------+
+1 row selected (0.063 seconds)
+
+SELECT TO_NUMBER('12345', '##0.##E0') FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 12345.0 |
++------------+
+1 row selected (0.069 seconds)
+</code></pre></div>
+<h2 id="to_time">TO_TIME</h2>
+
+<p>Converts a character string to a time.</p>
+
+<h3 id="to_time-syntax">TO_TIME Syntax</h3>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">TO_TIME (expression [, 'format'])
+</code></pre></div>
+<p><em>expression</em> is a character string enclosed in single quotation
marks or milliseconds, not enclosed in single quotation marks. </p>
+
+<p><em>'format'</em> is a format specifier enclosed in single
quotation marks that sets a pattern for the output formatting. Use this option
only when the expression is a character string, not milliseconds. </p>
+
+<h2 id="to_time-usage-notes">TO_TIME Usage Notes</h2>
+
+<p>Specify a format using patterns defined in <a
href="http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html";>Java
DateTimeFormat class</a>.</p>
+
+<h3 id="to_time-examples">TO_TIME Examples</h3>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_TIME('12:20:30', 'HH:mm:ss') FROM
sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 12:20:30 |
++------------+
+1 row selected (0.067 seconds)
+</code></pre></div>
+<p>Convert 828550000 milliseconds (23 hours 55 seconds) to the time.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT to_time(82855000) FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 23:00:55 |
++------------+
+1 row selected (0.086 seconds)
+</code></pre></div>
+<h2 id="to_timestamp">TO_TIMESTAMP</h2>
+
+<h3 id="to_timestamp-syntax">TO_TIMESTAMP Syntax</h3>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">TO_TIMESTAMP (expression [, 'format'])
+</code></pre></div>
+<p><em>expression</em> is a character string enclosed in single quotation
marks or a UNIX epoch timestamp, not enclosed in single quotation marks. </p>
+
+<p><em>'format'</em> is a format specifier enclosed in single
quotation marks that sets a pattern for the output formatting. Use this option
only when the expression is a character string, not a UNIX epoch timestamp. </p>
+
+<h3 id="to_timestamp-usage-notes">TO_TIMESTAMP Usage Notes</h3>
+
+<p>Specify a format using patterns defined in <a
href="http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html";>Java
DateTimeFormat class</a>. The TO_TIMESTAMP function takes a Unix epoch
timestamp. The TO_DATE function takes a UNIX epoch timestamp in
milliseconds.</p>
+
+<h3 id="to_timestamp-examples">TO_TIMESTAMP Examples</h3>
+
+<p>Convert a date to a timestamp. </p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_TIMESTAMP('2008-2-23 12:00:00',
'yyyy-MM-dd HH:mm:ss') FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 2008-02-23 12:00:00.0 |
++------------+
+</code></pre></div>
+<p>Convert Unix Epoch time to a timestamp.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_TIMESTAMP(1427936330) FROM sys.version;
++------------+
+| EXPR$0 |
++------------+
+| 2015-04-01 17:58:50.0 |
++------------+
+1 row selected (0.094 seconds)
+</code></pre></div>
+<p>Convert a UTC date to a timestamp offset from the UTC time zone code.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC',
'YYYY-MM-dd HH:mm:ss.s z') AS Original,
+ TO_CHAR(TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC',
'YYYY-MM-dd HH:mm:ss.s z'), 'z') AS New_TZ
+FROM sys.version;
+
++------------+------------+
+| Original | New_TZ |
++------------+------------+
+| 2015-03-30 20:49:00.0 | UTC |
++------------+------------+
+1 row selected (0.129 seconds)
+</code></pre></div>
+<h2 id="time-zone-limitation">Time Zone Limitation</h2>
+
+<p>Currently Drill does not support conversion of a date, time, or timestamp
from one time zone to another. The workaround is to configure Drill to use <a
href="http://www.timeanddate.com/time/aboututc.html";>UTC</a>-based time,
convert your data to UTC timestamps, and perform date/time operation in UTC.
</p>
+
+<ol>
+<li><p>Take a look at the Drill time zone configuration by running the
TIMEOFDAY function. This function returns the local date and time with time
zone information.</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TIMEOFDAY() FROM sys.version;
+
++------------+
+| EXPR$0 |
++------------+
+| 2015-04-02 15:01:31.114 America/Los_Angeles |
++------------+
+1 row selected (1.199 seconds)
+</code></pre></div></li>
+<li><p>Configure the default time zone format in <drill installation
directory>/conf/drill-env.sh by adding <code>-Duser.timezone=UTC</code> to
DRILL_JAVA_OPTS. For example:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">export DRILL_JAVA_OPTS="-Xms1G -Xmx$DRILL_MAX_HEAP
-XX:MaxDirectMemorySize=$DRILL_MAX_DIRECT_MEMORY -XX:MaxPermSize=512M
-XX:ReservedCodeCacheSize=1G -ea -Duser.timezone=UTC"
+</code></pre></div></li>
+<li><p>Restart SQLLine.</p></li>
+<li><p>Confirm that Drill is now set to UTC:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TIMEOFDAY() FROM sys.version;
+
++------------+
+| EXPR$0 |
++------------+
+| 2015-04-02 17:05:02.424 UTC |
++------------+
+1 row selected (1.191 seconds)
+</code></pre></div></li>
+</ol>
+
+<p>You can use the âzâ option to identify the time zone in TO_TIMESTAMP to
make sure the timestamp has the timezone in it. Also, use the âzâ option to
identify the time zone in a timestamp using the TO_CHAR function. For
example:</p>
+<div class="highlight"><pre><code class="language-text"
data-lang="text">SELECT TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC',
'YYYY-MM-dd HH:mm:ss.s z') AS Original,
+ TO_CHAR(TO_TIMESTAMP('2015-03-30 20:49:59.0 UTC',
'YYYY-MM-dd HH:mm:ss.s z'), 'z') AS TimeZone
+ FROM sys.version;
+
++------------+------------+
+| Original | TimeZone |
++------------+------------+
+| 2015-03-30 20:49:00.0 | UTC |
++------------+------------+
+1 row selected (0.299 seconds)
+</code></pre></div>
+<!-- DRILL-448 Support timestamp with time zone -->
+
+<!-- Apache Drill
+Apache DrillDRILL-1141
+ISNUMERIC should be implemented as a SQL function
+SELECT count(columns[0]) as number FROM dfs.`bla` WHERE ISNUMERIC(columns[0])=1
+ -->
+
+
+
+ <div class="doc-nav">
+
+ <span class="previous-toc"><a href="/docs/math-and-trig/">â Math and
Trig</a></span><span class="next-toc"><a
href="/docs/date-time-functions-and-arithmetic/">Date/Time Functions and
Arithmetic â</a></span>
+</div>
+
+
+ </div>
+ </div>
+ </div>
+
+ </div>
+ <p class="push"></p>
+<div id="footer" class="mw">
+<div class="wrapper">
+Copyright © 2012-2014 The Apache Software Foundation, licensed under the
Apache License, Version 2.0.<br>
+Apache and the Apache feather logo are trademarks of The Apache Software
Foundation. Other names appearing on the site may be trademarks of their
respective owners.<br/><br/>
+</div>
+</div>
+
+ <script>
+(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
+(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
+m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
+})(window,document,'script','//www.google-analytics.com/analytics.js','ga');
+
+ga('create', 'UA-53379651-1', 'auto');
+ga('send', 'pageview');
+</script>
+
+</body>
+</html>
