PureData / Netezza – What date/time ranges are supported by Netezza?

SQL (Structured Query Language), Date/Time ranges supported by Netezza

Date/Time ranges supported by Netezza

Here is a synopsis of the temporal ranges ( date, time, and timestamp), which Netezza / PureData supports.

Temporal Type

Supported Ranges

Size In Bytes

Date

A month, day, and year. Values range from January 1, 0001, to December 31, 9999. 4 bytes

Time

An hour, minute, and second to six decimal places (microseconds). Values range from 00:00:00.000000 to 23:59:59.999999. 8 bytes

Related References

Temporal data types

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Data types, Temporal data types

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza user-defined functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations

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

SFDC – Using a timestamp literal in a where clause

Salesforce Connector

Salesforce Connector

Working with timestamp literals in the Infosphere SFDC Connector soql is much like working date literals.  So, here a quick example which may save you some time.

SOQL Timestamp String Literals Where Clause Rules

Basically, the timestamp pattern is straight forward and like the process for dates, but there are some differences. The basic rules are for a soql where clause:

  • No quotes
  • No functions
  • No Casting function, or casting for the where soql where clause to read it
  • It only applies to datetime fields
  • A Timestamp identifier ‘T’
  • And the ISO 1806 time notations

Example SOQL Timestamp String Literals

So, here are a couple of timestamp string literal examples in SQL:

  • 1901-01-01T00:00:00-00:00
  • 2016-01-31T00:00:00-00:00
  • 9999-10-31T00:00:00-00:00

Example SQL with Timestamp String Literal Where Clause

 

Select e.Id,

e.AccountId,

e.StartDateTime

From Event e

WHERE e.StartDateTime > 2014-10-31T00:00:00-00:00

 

Related References

Salesforce Developer Documentation

Home, Developer Documentation, Force.com SOQL and SOSL Reference

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

Salesforce Workbench

Home, Technical Library, Workbench

W3C

Date Time Formats

 

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

Netezza / PureData – ERROR [HY000] ERROR: Bad timestamp external representation ‘0000-00-00’

SQL (Structured Query Language),  Bad timestamp external representation '0000-00-00'

Bad timestamp external representation ‘0000-00-00’

While working on a recent data conversion a data timestamp error occurred, where the legacy code and data was ‘0000-00-00’ default stamp, which was causing errors in Netezza/PureData Analytics (PDA).  ‘0000-00-00’ is not a valid timestamp in Netezza and will produce a ‘ ERROR [HY000] ERROR:  Bad timestamp external representation ‘0000-00-00’’ notice.

When handling default dates in SQL and ETL’s, generally speaking, ‘1901-01-01’ or ‘0001-01-01’ as use as defaults for null and/or invalid dates.  Both of these dates and or timestamps will insert into Netezza, which I used a quick, simple, proof table to demonstrate, which were SQL extracted and in the table below.

Valid Defaults dates in Netezza

TEST_SK TEST_TS
1 1901-01-01 00:00:00
2  00:00:00

Related References

Temporal data types

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Data types, Temporal data types

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza user-defined functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations

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 / PureData – How to convert an epoch field to a timestamp

Netezza PureData convert an epoch field to a timestamp

How To Convert And Epoch Field To A Timestamp

This is one of the item, which we need to do in Netezza SQL from time to time, but is not exactly obvious to the average SQL user.  So, having a pattern for making the conversion for a epoch field to a timestamp can be a real time saver.

Epoch Field Conversion to A Timestamp Format

to_timestamp(‘1970-01-01 00:00:00′,’YYYY-MM-DD HH24:MI:SS’) + (<> * interval ‘.000001 seconds’) as Entry_TS_Timestamp_Format

Epoch Timestamp Conversion Example SQL 1

select

entry_ts as Orginial_Format_Entry_TS,

to_timestamp(‘1970-01-01 00:00:00′,’YYYY-MM-DD HH24:MI:SS’) + (entry_ts * interval ‘.000001 seconds’) as Entry_TS_Timestamp_Format

from _v_system_util

limit 1;

Epoch Timestamp Conversion Example SQL 2

select

entry_ts as Orginial_Format_Entry_TS,

to_date(‘1/1/1970′,’MM/DD/YYYY’) + (entry_ts * interval ‘.000001 seconds’) as Entry_TS_Timestamp_Format

from _v_system_util

limit 1;

Related References

PS Query Timestamp to Date Logic

Oracle Peoplesoft Query Timestamp to Date Logic

Oracle Peoplesoft

Converting a timestamp to a date in PeopleSoft Query seems to be a common item, which I have helped others with, to the point that I kept a personal note on the coding logic.  So, I thought I would post this quick note in case it may be useful to someone.  This item would seem to be straight forward, however, PS query tries to help you with the process, so, some experimentation is usually involved to get the code to out just right.

Here is a sample expression I use for the timestamp to date conversion in PS Query.

  • Create an expression with an Expression Type of “Number
  • Set Length to 10
  • Sample code: TO_DATE(SUBSTR(<<RecordAlias.TimestampFieldName>>,1,10))
  • PS Query interpretation: TO_DATE(SUBSTR(TO_CHAR(CAST((<<RecordAlias.TimestampFieldName>>) AS TIMESTAMP),’YYYY-MM-DD-HH24.MI.SS.FF’),1,10))

Related References