Linux Bash Prompt

Here is an useful prompt which shows the returncode and time duration in a colorful style. It must be inserted in your .bashrc or .profile

function timer_now {
    date +%s%N
}

function timer_start {
    timer_start=${timer_start:-$(timer_now)}
}

function timer_stop {
    local delta_us=$((($(timer_now) - $timer_start) / 1000))
    local us=$((delta_us % 1000))
    local ms=$(((delta_us / 1000) % 1000))
    local s=$(((delta_us / 1000000) % 60))
    local m=$(((delta_us / 60000000) % 60))
    local h=$((delta_us / 3600000000))
    # Goal: always show around 3 digits of accuracy
    if ((h > 0)); then timer_show=${h}h${m}m
    elif ((m > 0)); then timer_show=${m}m${s}s
    elif ((s >= 10)); then timer_show=${s}.$((ms / 100))s
    elif ((s > 0)); then timer_show=${s}.$(printf %03d $ms)s
    elif ((ms >= 100)); then timer_show=${ms}ms
    elif ((ms > 0)); then timer_show=${ms}.$((us / 100))ms
    else timer_show=${us}us
    fi
    unset timer_start
}


set_prompt () {
    Last_Command=$? # Must come first!
    Blue='\[\e[01;34m\]'
    White='\[\e[01;37m\]'
    Red='\[\e[01;31m\]'
    Yellow='\[\e[01;33m\]'
    Green='\[\e[01;32m\]'
    Reset='\[\e[00m\]'
    FancyX='\342\234\227'
    Checkmark='\342\234\223'

    echo -ne "\033]0;${HOSTNAME} - ${USER}\007"

    # Add a bright white exit status for the last command
    PS1="$White\$? "
    # If it was successful, print a green check mark. Otherwise, print
    # a red X.
    if [[ $Last_Command == 0 ]]; then
        PS1+="$Green$Checkmark "
    else
        PS1+="$Red$FancyX "
    fi

    # Add the ellapsed time and current date
    timer_stop
    PS1+="($timer_show) $White\t "

    # If root, just print the host in red. Otherwise, print the current user
    # and host in green.
    if [[ $EUID == 0 ]]; then
        PS1+="$Red\\u$Green@$Yellow\\h "
    else
        PS1+="$Green\\u@$Yellow\\h "
    fi
    # Print the working directory and prompt marker in blue, and reset
    # the text color to the default.
    PS1+="$Blue\\w \\\$$Reset "
}

trap 'timer_start' DEBUG
PROMPT_COMMAND='set_prompt'


umask 022
export EDITOR='vim'
export LS_OPTIONS='--color=auto'
eval "`dircolors`"
alias ls='ls $LS_OPTIONS'
alias ll='ls $LS_OPTIONS -l'
alias l='ls $LS_OPTIONS -lA'
#
# Some more alias to avoid making mistakes:
alias rm='rm -i'

SQL Timestamp

If you’re selecting by date only, base your calculations on CURDATE (which returns date only) rather than NOW (which returns date and time). These examples will catch all times within the day ranges:

  • Today: WHERE timestamp >= CURDATE()
  • Yesterday: WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND timestamp < CURDATE()
  • This month: WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)
  • Between the two dates 3 June 2013 and 7 June 2013 (note how the end date is specified as 8 June, not 7 June): WHERE timestamp >= '2013-06-03' AND timestamp < '2013-06-08'

The „this week“ depends on which day you start your week; I’ll leave that to you. You can use the DAYOFWEEK function to tweak CURDATE() to the proper ranges.

Addendum: OP’s column type was INTEGER, storing a UNIX timestamp, and my answer assumed the column type was TIMESTAMP. Here’s how to do all the same things with a UNIX timestamp value and still maintain optimization if the column is indexed (as the answers above will do if the TIMESTAMP column is indexed)…

Basically, the solution is to just wrap the beginning and/or ending dates in the UNIX_TIMESTAMP function:

  • Today: WHERE timestamp >= UNIX_TIMESTAMP(CURDATE())
  • Yesterday: WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) AND timestamp < UNIX_TIMESTAMP(CURDATE())
  • This month: WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY))
  • Between the two dates 3 June 2013 and 7 June 2013 (note how the end date is specified as 8 June, not 7 June): WHERE timestamp >= UNIX_TIMESTAMP('2013-06-03') AND timestamp < UNIX_TIMESTAMP('2013-06-08')