Reporting on your After Hours using Git commit timestamps

Sometimes its hard to keep yourself accountable to the extra long hours you work, and most of the time you don't want to show off how late you've been working, but every now and then your manager would probably ask if you've been putting in the necessary hours.

If thats the case, git commits can give you a certain level of transparency provided you commit regularly.

The following bash script assumes you have all your git repositories cloned to a folder specified in GITFOLDER which should be updated to your local setup. It will run a simple "git log --author" command for a given USERNAME and list only the timestamps after the hours of 5pm to the hours of 6am:

#!/usr/bin/env sh

USERNAME="<username>"
GITFOLDER="${HOME}/dev"

cd ${GITFOLDER}
FOLDERS=$(ls)

while IFS= read -r FOLDER
do
    cd "${GITFOLDER}/${FOLDER}"

    TIME_LOGS=$(git log --author="${USERNAME}" | grep "Date:" | grep -E '\s1[7-9]+\:|\s2[0-3]+\:|\s0[0-6]+\:')
    
    while IFS= read -r TIME_LOG
    do
        if [ ! -z "$TIME_LOG" ]
        then
            COMMIT_DATE=$(echo ${TIME_LOG} | sed -e "s/Date: //g")
            echo "${COMMIT_DATE} ${FOLDER}"
        fi
    done <<< "${TIME_LOGS}"

done <<< "${FOLDERS}"

Reporting on your After Hours in SnowFlake

My current work at Tyro is predominantly in the Snowflake Data Warehouse creating reports, building queries, and maintaining the DDL.

As such it is necessary to keep monitoring my constant usage as the team starts to grow and engineers become more accountable.

One good technique is to query the built-in view:

SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

It consists of the USER_NAME, ROLE_NAME, and even the QUERY_TEXT of query that was run in snowflake.

What we need in order to find out the number of after hours worked on a day to day basis is the START_TIME of the query that was executed. With that we can then filter by any queries run after 5PM (i.e. the 17th hour of the day), and the difference between max and min dates for each day to find out how many hours were worked after that time.

SELECT
    USER_NAME,
    ROUND(TIMESTAMPDIFF('MINUTE', MIN(START_TIME), MAX(START_TIME)) / 60, 2) AFTER_HOURS,
    DAYNAME(MIN(START_TIME)) DAY,
    MIN(START_TIME) START_DATE,
    MAX(START_TIME) END_DATE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE USER_NAME = '<user_name>'
    AND DATE_PART('HOUR', START_TIME) > 17
GROUP BY
    USER_NAME,
    DATE_PART('DAY', START_TIME),
    DATE_PART('MONTH', START_TIME),
    DATE_PART('YEAR', START_TIME)
ORDER BY MIN(START_TIME) DESC;