Cleaning Data Archives | Data makes sense.

Get yesterday’s date in a windows batch file – correct version

Hi all,

If you use a windows server and get yesterday’s date for something like a download, you might probably use this popular script that can be found here and there. Unfortunately, the script is somewhat broken when dealing with getting the last day of the month. The logic is solid, but somewhere between the idea and implementation, something was lost.

see below the working, correct version:


@setlocal enableextensions enabledelayedexpansion
rem Get the date from WMI (on one line).

for /f "skip=2 tokens=2-7 delims=," %%A in ('wmic
path win32_localtime get day^,month^,year^ /format:csv') do (
set /a "yest_yyyy = %%C"
set /a "yest_mm = %%B"
set /a "yest_dd = %%A"
)

rem Not the first of the month, just decrement day.

if not %yest_dd%==1 (
set /a yest_dd = yest_dd - 1
goto done
)

rem Jan 1, set to Dec 31 previous year.

if %yest_mm%==1 (
set /a "yest_dd = 31"
set /a "yest_mm = 12"
set /a "yest_yyyy = yest_yyyy - 1"
goto :done
)

rem Any other day, decrement month.

set /a "yest_mm = yest_mm - 1"

rem Need to find last day, default to 31.

set dim=31

rem Apr/Jun/Sep/Nov all have 30 days. Feb gets special handling.

if %yest_mm%==4 set dim=30
if %yest_mm%==6 set dim=30
if %yest_mm%==9 set dim=30
if %yest_mm%==11 set dim=30
if not %yest_mm%==2 goto :got_dim

rem Default Feb to 28 then use rules to override.

set dim=28

set /a "divid=yest_yyyy%%400"
if "%divid%"=="0" goto daysinmonth_29days
set /a "divid=yest_yyyy%%100"
if "%divid%"=="0" goto :done
set /a "divid=yest_yyyy%%4"
if not "%divid%"=="0" goto :got_dim

rem Adjust to 29 days.

:daysinmonth_29days

set dim=29

:got_dim
set yest_dd=%dim%

:done

 

rem Pad out and return value.

if %yest_mm% lss 10 set yest_mm=0%yest_mm%
if %yest_dd% lss 10 set yest_dd=0%yest_dd%

set yesterday=%yest_yyyy%-%yest_mm%-%yest_dd%

endlocal &amp;&amp; set yesterday=%yesterday%

@setlocal enableextensions enabledelayedexpansion

rem Get the date from WMI (on one line).

for /f "skip=2 tokens=2-7 delims=," %%A in ('wmic

path win32_localtime get day^,month^,year^ /format:csv') do (

set /a "yest_yyyy = %%C"

set /a "yest_mm = %%B"

set /a "yest_dd = %%A"

)

rem Not the first of the month, just decrement day.

if not %yest_dd%==1 (

set /a yest_dd = yest_dd - 1

goto done

)

rem Jan 1, set to Dec 31 previous year.

if %yest_mm%==1 (

set /a "yest_dd = 31"

set /a "yest_mm = 12"

set /a "yest_yyyy = yest_yyyy - 1"

goto :done

)

rem Any other day, decrement month.

set /a "yest_mm = yest_mm - 1"

rem Need to find last day, default to 31.

set dim=31

rem Apr/Jun/Sep/Nov all have 30 days. Feb gets special handling.

if %yest_mm%==4 set dim=30

if %yest_mm%==6 set dim=30

if %yest_mm%==9 set dim=30

if %yest_mm%==11 set dim=30

if not %yest_mm%==2 goto :got_dim

rem Default Feb to 28 then use rules to override.

set dim=28

set /a "divid=yest_yyyy%%400"

if "%divid%"=="0" goto daysinmonth_29days

set /a "divid=yest_yyyy%%100"

if "%divid%"=="0" goto :done

set /a "divid=yest_yyyy%%4"

if not "%divid%"=="0" goto :got_dim

rem Adjust to 29 days.

:daysinmonth_29days

set dim=29

:got_dim

set yest_dd=%dim%

:done

rem Pad out and return value.

if %yest_mm% lss 10 set yest_mm=0%yest_mm%

if %yest_dd% lss 10 set yest_dd=0%yest_dd%

set yesterday=%yest_yyyy%-%yest_mm%-%yest_dd%

endlocal && set yesterday=%yesterday%

Normalizing codes, mySQL extracting alphanumeric functions

Wanted to share an sql function I end up using quite frequently when dealing with user input data – normalization to alphanumeric. It can also be modified into a function to extract numeric values. If you do modify it to that, make sure you keep returns varchar for the cases of extracting numeric values beginning with 0. You can also add other characters, such as the minus sign, if required.

Example use cases:

Matching producer codes:

select producer_id, lcase(alphanumeric(producer_id)) from table
&gt;'BLA-bla-541' ; 'blabla541'
&gt;'bla Bla+/541'; 'blabla541'

select producer_id, lcase(alphanumeric(producer_id)) from table

>'BLA-bla-541' ; 'blabla541'

>'bla Bla+/541'; 'blabla541'

Normalizing/matching phone numbers:

select phone_number, alphanumeric(replace(phone_number, '+', '00') from table
&gt;'+45 097 . 4521/4761' ; '004509745214761'
&gt;'0045/(0)97.4521.4761'; '004509745214761'

select phone_number, alphanumeric(replace(phone_number, '+', '00') from table

>'+45 097 . 4521/4761' ; '004509745214761'

>'0045/(0)97.4521.4761'; '004509745214761'

function:

DELIMITER !

DROP FUNCTION IF EXISTS alphanumeric!

CREATE FUNCTION alphanumeric ( str VARCHAR(255) )
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE ret VARCHAR(255) DEFAULT '';
DECLARE c VARCHAR(1);
SET len = CHAR_LENGTH( str );
REPEAT
BEGIN
SET c = MID( str, i, 1 );
IF  c REGEXP '[[:alpha:]]' or c REGEXP '[[:digit:]]' THEN -- Change matching here
SET ret=CONCAT(ret,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN ret;
END !
DELIMITER ;

DELIMITER !

DROP FUNCTION IF EXISTS alphanumeric!

CREATE FUNCTION alphanumeric ( str VARCHAR(255) )

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN

DECLARE i, len SMALLINT DEFAULT 1;

DECLARE ret VARCHAR(255) DEFAULT '';

DECLARE c VARCHAR(1);

SET len = CHAR_LENGTH( str );

REPEAT

BEGIN

SET c = MID( str, i, 1 );

IF c REGEXP '[[:alpha:]]' or c REGEXP '[[:digit:]]' THEN -- Change matching here

SET ret=CONCAT(ret,c);

END IF;

SET i = i + 1;

END;

UNTIL i > len END REPEAT;

RETURN ret;

END !

DELIMITER ;

Cleaning noisy data: Postgresql Mode and Modal Share aggregate function

Calculating mode and modal share on postgresql has never been easier than just running these functions.

Some background:
I needed to calculate the aggregate mode and the frequency of that mode to be able to draw some conclusions about the stability of some particularly noisy data. Sample standard deviation can be very misleading for measuring data noise, as it’s a mix of amplitude of variation and frequency.

Imagine measuring the best temperature to drink wine based on what people would report.

Some would say for a particular wine the temperature would be 10 celsius, while many others will say 11-12 degrees. As we are talking about user input, someone may jokingly recommend drinking it at -20 Celsius or at 120 Celsius. The data cannot be trusted.

So let’s take the array recommended_temperature [8,10,10,12,11,10,10,12,10,11,-20,200,10,10]

Finding the right temperature to serve the wine at from this array can be tricky. Data is noisy and not to be trusted.

Total Numbers: 14

Mean (Average): 21.71429

Standard deviation:51.95624

Variance(Standard deviation):2699.45055

Do any of the above numbers even come close? No. You would not serve the wine at an average of 22 Celsius, and the standard deviation is high, but that doesn’t tell you if the values are spread, or if an outlier is messing everything up.

What about the mode? Mode in our case is 10 (the most common value)
Sounds reasonable, wine tastes nice at 10 degrees. But how sure are we that this is the right temperature?
We need more info, mainly modal share (number of values in the mode/all values)

For that, I put together in PostgreSQL a mode_count function, to allow you to find how many values you have in the mode, and possibly calculate a % of all values.
so now we have Mode:10;Modal_share 0.5 (7/14)
We can say with 50% certainty that the appropriate temperature to serve the wine at, based on user recommendations, is 10. Add in the high standard deviation, and you will easily detect the fact that there are strong outliers present in the data, giving you even more confidence towards your mode value.

The function from postgres manual for mode:

CREATE OR REPLACE FUNCTION _final_mode(anyarray)
RETURNS anyelement AS
$BODY$
SELECT a
FROM unnest($1) a
GROUP BY 1
ORDER BY COUNT(1) DESC, 1
LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;

<em id="__mceDel" style="line-height: 1.714285714; font-size: 1rem;"><em id="__mceDel">DROP AGGREGATE IF EXISTS mode(anyelement);</em></em>
CREATE AGGREGATE mode(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=_final_mode,
INITCOND='{}'
);

CREATE OR REPLACE FUNCTION _final_mode(anyarray)

RETURNS anyelement AS

$BODY$

SELECT a

FROM unnest($1) a

GROUP BY 1

ORDER BY COUNT(1) DESC, 1

LIMIT 1;

$BODY$

LANGUAGE 'sql' IMMUTABLE;

DROP AGGREGATE IF EXISTS mode(anyelement);

CREATE AGGREGATE mode(anyelement) (

SFUNC=array_append,

STYPE=anyarray,

FINALFUNC=_final_mode,

INITCOND='{}'

);

and the custom mode count function:

CREATE OR REPLACE FUNCTION _final_mode_count(anyarray)
RETURNS bigint AS
$BODY$
SELECT COUNT(*)
FROM unnest($1) a
GROUP BY a
ORDER BY COUNT(1) DESC, a
LIMIT 1;
$BODY$
LANGUAGE 'sql' IMMUTABLE;

-- Tell Postgres how to use our aggregate
DROP AGGREGATE IF EXISTS mode_count(anyelement);
CREATE AGGREGATE mode_count(anyelement) (
SFUNC=array_append, --Function to call for each row. Just builds the array
STYPE=anyarray,
FINALFUNC=_final_mode_count, --Function to call after everything has been added to array
INITCOND='{}' --Initialize an empty array when starting
);

CREATE OR REPLACE FUNCTION _final_mode_count(anyarray)

RETURNS bigint AS

$BODY$

SELECT COUNT(*)

FROM unnest($1) a

GROUP BY a

ORDER BY COUNT(1) DESC, a

LIMIT 1;

$BODY$

LANGUAGE 'sql' IMMUTABLE;

-- Tell Postgres how to use our aggregate

DROP AGGREGATE IF EXISTS mode_count(anyelement);

CREATE AGGREGATE mode_count(anyelement) (

SFUNC=array_append, --Function to call for each row. Just builds the array

STYPE=anyarray,

FINALFUNC=_final_mode_count, --Function to call after everything has been added to array

INITCOND='{}' --Initialize an empty array when starting

);

They are aggregate functions, so call them as:

SELECT 
mode(some_value) AS modal_value,
mode_count(some_value) as count_at_mode,
mode_count(some_value)/(count(some_value)::float as modal_share
<span style="font-size: 0.857142857rem; line-height: 1.714285714;">FROM t;</span>

SELECT

mode(some_value) AS modal_value,

mode_count(some_value) as count_at_mode,

mode_count(some_value)/(count(some_value)::float as modal_share

FROM t;

Hope you enjoy!