Netezza / PureData – How to convert a timestamp to date in SQL

Netezza Convert Timestamp to Date

Netezza Convert Timestamp to Date

 

Since timestamps are integer, which are numbers, the date function can easily convert a timestamp to a date in Netezza SQL.

Date Function Arguments, per IBM Documentation

First argument data type Second argument data type Returns
date numeric(8,0) date

Example Timestamp to Date Conversion SQL

In this example CREATEDATE is stored as a timestamp, despite the field name.

SELECT

DATE(CREATEDDATE)

, COUNT(*) as CNT

FROM Blog.CASES_FS

GROUP BY

date(CREATEDDATE)

ORDER BY

date(CREATEDDATE);

Related References

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.0, IBM Netezza User-Defined Functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations

PureData / Netezza – Useful links

IBM Netezza / IBM PureData for Analytics Useful links

IBM Netezza / IBM PureData for Analytics

Here are a few Netezza references, which I have found useful.

Reference Type Link
Vendor Documentation https://www.ibm.com/support/knowledgecenter/SSULQD_7.2.0/com.ibm.nz.welcome.doc/doc/welcome.html
IBM Developer Works https://www.ibm.com/developerworks/community/groups/service/html/communitystart?communityUuid=9c8f1300-9ac0-4de5-80e6-0
Aginity Workbench for Netezza http://www.aginity.com/workbench/netezza/
IBM Netezza Database User’s Guide https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.0/com.ibm.nz.dbu.doc/c_dbuser_ntz_sql_introduction.html
Client Software Packages https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.0/com.ibm.nz.adm.doc/c_sysadm_client_software_packages.html
Netezza Client Software Installation https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.0/com.ibm.nz.adm.doc/c_sysadm_client_software_install.html

Related References