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
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.
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
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.
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
now() as “Time”,
current_date as “Today”,
when (DATE_PART(‘HOUR’,NOW())>12 )
THEN date(current_date + cast(‘1 days’ as interval))