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
I had reason today to get the number of the day of the week, in PureData / Netezza, which I don’t seem to have discussed in previous posts. So, here is a simple script to get the number for the day of week with a couple of flavors, which may prove useful.
select extract(dow from <<FieldName>>) from <<SchemaName>>.<<tableName>>
, TO_CHAR(CURRENT_DATE,’DAY’) AS DAY_OF_WEEK
—WEEK STARTS ON MONDAY
, EXTRACT(DOW FROM CURRENT_DATE)-1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_MONDAY
—WEEK STARTS ON SUNDAY
, EXTRACT(DOW FROM CURRENT_DATE) AS DAY_OF_WEEK_NUMBER_STARTS_ON_SUNDAY
—WEEK STARTS ON SATURDAY
, EXTRACT(DOW FROM CURRENT_DATE)+1 AS DAY_OF_WEEK_NUMBER_STARTS_ON_SATURDAY
PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Conversion functions, Template patterns for date/time conversions
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