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

Advertisements

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

Netezza / PureData – CURRENT_TIMESTAMP Function

Netezza PureData Analytics CURRENT_TIMESTAMP Function

Netezza / PureData Analytics Current Timestamp

The Current_Timestamp function, returns the current date and time, from PureData Analytics (Netezza) and is an easy way to retrieve the current timestamp via SQL.

Example SQL

Select CURRENT_TIMESTAMP as “Current Time”

from _V_DUAL;

Related Reference

Netezza / PureData – how to set processing date to next day based on a timestamp

hey are a variety of uses for this logic within Netezza / Puredata SQL, when working with interfaces, ETL, ELT, Snapshot facts and/or tables, setting the processing / run control date, and the list goes on.  However, rather than reinventing this logic each time, I thought I would save this code snippet for future use and reference.

What this code does is straight forwarded enough, it:

  • Determines the current time of day using the Now()
  • Determines the our from the time using the Date_Part function
  • Determines the current date using the current_date function
  • Then, using the Case function set the processing date based on the current hour of the day

SQL used to calculate processing date

Select

now() as “Time”,

current_date as “Today”,

(case

when (DATE_PART(‘HOUR’,NOW())>12 )

THEN date(current_date +  cast(‘1 days’ as interval))

ELSE  current_date

END ) as “PROCESSING_DATE”

From ADMIN._v_dual;

·       Morning Example of SQL Results

Processing Date Morning Example

Processing Date Morning Example

·       Afternoon Example of SQL Results

Processing Date Afternoon Hour Example

Processing Date After Noon Hour Example

Related References

 

Netezza / PureData – Now() Command For Current Date

PureData Analytics Now() Command

PureData Analytics Now() Command

There is more than one way to retrieve current date information from PureData Analytics (Netezza).  Using the Now() command is an easy way to retrieve current date from within SQL.

 

Example SQL

Select now() as “Today”

from _V_DUAL;

 

Related Reference

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.1.0, IBM Netezza Database User’s Guide, Netezza SQL basics, Netezza SQL extensions,Date/time functions