MySQL date display format conversion

MySQL date time conversion functions: 

• UNIX_TIMESTAMP(date) docs
• FROM_UNIXTIME(epoch) docs

Examples:

select name, from_unixtime(time_added, '%Y-%m-%d %h:%i')

from company

order by date_added desc

limit 10; -- list the most recently added companies

You may also omit the '%Y-%m-%d %h:%i' format string, to get the default format YYYY-MM-DD HH:MM:SS, e.g. `2023-09-07 09:43:51`

Change timezone in Linux

Change this link:

$ ls -l /etc/localtime

lrwxrwxrwx    1 root     dwebadm        34 Dec  2 01:12 /etc/localtime -> /usr/share/zoneinfo/Asia/Singapore*

See all commit messages in svn log

After merging a branch to trunk, subversion does not include commit logs from branches in the trunk log. With a little work it can be persuaded to display the log in a more useful way, see "svnlogg" perl script below.

When viewing this log output you may want to see the diff of one of the changes and naively type:

    svn diff -c 69271

...this may produce no output. However, if you type this intead:

    svn diff -c 69271 https://www.example.com/repo/branches/feature_xyz

...then you will see the expected output.

How to determine the branch name from the commit message is left as an exercise for the reader.


"svnlogg" perl script:

#!/usr/bin/env perl

# First, get all the commits.
# -v shows the filenames of changed files,
# -g shows commits from all merged branches.

my $LOG_TEMP = "/tmp/log.txt";

system("svn log -v -g > $LOG_TEMP");

# Then split the list into individual commits, filter out ones we don't want to see, and sort by date:

open(my $fh,"<",$LOG_TEMP) or die "cannot open file $LOG_TEMP";
local $/="------------------------------------------------------------------------"; # define the record separator
my @a = <$fh>; # populate an array with the commit messages
foreach my $aa (
    sort {
        # sort the commits by date (descending)
        my ($c) = $a =~ /\s\|\s(\d\d\d\d\-\d\d\-\d\d)\s/; # extract date field
        my ($d) = $b =~ /\s\|\s(\d\d\d\d\-\d\d\-\d\d)\s/; #
        $d cmp $c
    } @a
) {
    # exclude unwanted messages, especially the hundreds of "remove svn:mergeinfo"
    # also exclude the commit messages we use by convention in our organisation
    # to refer to branches and merges
    if ($aa !~ /(remove svn:mergeinfo|branching|final pull)/i) {
        print "$aa\n";
    }
}

Depending on your hardware it may be unusably slow, so pipe it into a file for viewing later.

Temporarily skip a test until a specific date

The job of a test suite is to alert the developers of a potential problem. Once that's happened, the only value in continuing to fail is to nag and remind them to make a change. In real life sometimes fixing issues takes a long time. If they're already tracking changes in a ticketing system, or actively working on a fix, the failure becomes noise, and can hide other unexpected failures. There should be no such thing as an "okay failure" which can be ignored - you need to stop it failing if the fix is already planned. But you don't want to risk forgetting about it. So you can disable it for a short time, to allow the developer to fix the underlying issue. In the future, perhaps CI frameworks might feature a Nagios-style "acknowledge" function.

This is one solution for now:

use Test::More;
use DateTime;

my $expected_to_fail = (
    ($test_data = 'foo')
    &&
    ! deadline_has_passed( DateTime->new( year => 2014, month => 11, day => 23 ) # the future
) ? 1 : 0;

SKIP: {
    skip "while we do something (ticket ID)", 1 if $expected_to_fail;
    ok( "some test" );
}

sub deadline_has_passed {
    my ($deadline) = @_;
    my $today = DateTime->now; #->add( days => 30 );
    my $deadline_has_passed = (DateTime->compare( $today, $deadline ) < 1) ? 0 : 1;

    # debug
    #note("today    = ".$today->ymd);
    #note("deadline = ".$deadline->ymd);
    #note("compare today,deadline => ". DateTime->compare( $today, $deadline ));
    #note("has deadline passed? $deadline_has_passed");

    return $deadline_has_passed;
}

Determine a day's ordinal suffix: 1st, 2nd, 3rd, 4th, etc.

e.g. for dates you don't want to display "19 July" but rather "19th July":

my $ordinal;
if ($foo =~ /(?<!1)1$/) {
    $ordinal = 'st';
} elsif ($foo =~ /(?<!1)2$/) {
    $ordinal = 'nd';
} elsif ($foo =~ /(?<!1)3$/) {
    $ordinal = 'rd';
} else {
    $ordinal = 'th';
}

Bonus points if you can integrate this with DateTime's strftime() method neatly.

(source)

Change the date of a git commit

Author date:

git commit --amend --date="Wed Feb 16 14:00 2011 +0100"

Commit date:

GIT_COMMITTER_DATE="Wed Feb 16 14:00 2011 +0100" git commit --amend

You've got to love git.

(source)

How to fix ntpdate

# set 'server' value in config to the correct ntp date server on your network
$ sudo vim /etc/ntp.conf

# force update (1.2.3.4 is your ntp date server)
$ sudo /etc/init.d/ntpd stop
$ sudo /usr/sbin/ntpdate 1.2.3.4
$ sudo /etc/init.d/ntpd start


# check it worked (offset should be < 10)

$ ntpq -pn
     remote           refid      st t when poll reach   delay   offset  jitter
==============================================================================
 1.2.3.4            6.7.8.3      3 u    6   64    1    3.902   -0.216   0.001
 1.2.3.5            6.7.8.4      3 u    5   64    1    1.753    0.048   0.001

Set the date in linux

# date +%Y%m%d --set "2013-05-20"
# date +%H%M%S --set "15:30:00"

Easy way to convert epoch seconds to human readable date in Perl

Easiest way:

print scalar localtime(946684800);

Prints:

Sat Jan  1 00:00:00 2000

Next easiest, more flexible:

require POSIX; print POSIX::strftime("%Y-%m-%d %H:%M:%S", localtime((946684800)));

Prints:

2000-01-01 00:00:00

Easiest way to find yesterday's date in Perl

perl -le'my ($year, $month, $day) = (localtime(time - (1 * 24 * 60 * 60)))[5,4,3]; $year += 1900; $month++; printf("%d-%02d-%02d", $year, $month, $day)'

Test MySQL date calculations

This is a way to test certain SQL queries involving dates. Say a query is supposed to select "Last Saturday", regardless of the date on which it runs:

SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(DATE_SUB(CURDATE(), INTERVAL 5 DAY)) DAY) AS 'Last Saturday';

How do you know if it really works on all days? This command will iterate through all the days in the month, and run the query with each date to see what it gets:

for day in $(seq 1 30)

do

    echo Nov $day, $(mysql -u sdkain -h db21 lf_wh -e"SELECT DATE_SUB('2010-11-$day', INTERVAL WEEKDAY(DATE_SUB('2010-11-$day', INTERVAL 6 DAY)) DAY) AS 'Last Sunday'")

done

Remember to test for when the year changes, and on leap years.

Perl: Simplest way to get a formatted date

use POSIX 'strftime';
my $now_string = strftime( "%Y%m%d", localtime );

Date/Time perl modules

• Date::Manip - Big. Needs several step sto convert to/from eopch seconds. But you could just subtract 1 week's worth of seconds.
• Date::Calc - Less big, simpler. Has Add_Delta_Days. Doesn't generate BST from epoch seconds (on Mac).
• POSIX - has strftime. Can easily generate epoch seconds from the current time. Lacking in conversion to date?
• DateTime - The best and easiest.
  

Crontab format (V3)

MAILTO=me@there.co.uk

# minute (0-59),
# | hour (0-23),
# | | day of the month (1-31),
# | | | month of the year (1-12),
# | | | | day of the week (0-6 with 0=Sunday).
# | | | | | commands
# 3 2,3,4 * * 0,6 /command to run on more than one day/hour
# 3 2,4-7 * * 1-5 /another/command to run on a range of days/hours

04 10 * * * /command/here 2>> /path/to/log/prog.$(date +\%Y\%m\%d).log

NOTES:

• You must escape percent signs with a backslash
• Putting them in double quotes doesn't work
• Putting in double quotes and backslashes works, but the backslashes are written as part of the filename. So don't so that.
  

Unix timezone settings

Time zone stuff in UNIX isn't difficult in concept but it can be tricky to work out how a box is configured.

The current timezone is set by the /etc/localtime file, which is either a symlink, or a hard link, or a copy of a file in /usr/share/zoneinfo. If the file's timestamp is a long time ago it's a fair bet it's been configured this way for a while.

The files in /usr/share/zoneinfo contain the definitions of a time zone -- its offset from UTC and any daylight savings in use. The problem is working out which file is in use. It could be a symlink:

[root@server ~]# ls -l /etc/localtime
lrwxrwxrwx 1 root root 27 Jun 26 2008 /etc/localtime -> /usr/share/zoneinfo/Etc/GMT

Given the timestamp on the symlink to the GMT zone file is June last year I'd say it's likely that it's been configured this way (ie, GMT) for a long time.

Other boxes don't have symlinked timezone files:

[root@server ~]# ls -l /etc/localtime
-rw-r--r-- 1 root root 118 Mar 4 2008 /etc/localtime

They might have a hard link (which is like having a file known by another name -- same contents, more than one name) to a zoneinfo file. You can try to work this out using find:

[root@server ~]# ls -l /etc/localtime
-rw-r--r-- 11 root root 118 Apr 13 2009 /etc/localtime

[root@server ~]# find /usr/share/zoneinfo -samefile /etc/localtime
/usr/share/zoneinfo/GMT0
/usr/share/zoneinfo/Greenwich
/usr/share/zoneinfo/GMT
/usr/share/zoneinfo/Etc/GMT0
/usr/share/zoneinfo/Etc/Greenwich
/usr/share/zoneinfo/Etc/GMT
/usr/share/zoneinfo/Etc/GMT-0
/usr/share/zoneinfo/Etc/GMT+0
/usr/share/zoneinfo/GMT-0
/usr/share/zoneinfo/GMT+0

You can see here that /etc/localtime and all the GMT zone files are hard links to the same file contents -- ie, the file contents that set the time to GMT, showing this box is in GMT.

If /etc/localtime isn't a symlink or a hard link it must be a straight copy of a zone file (or it's totally broken). You can work this out by MD5 summing /etc/localtime's contents against all the possible time zone files it could be. Get the MD5 sum (like an abbreviation of the contents) of /etc/localtime:

[root@server ~]# md5sum /etc/localtime
fcccbcf95c718cf2fdee557763e460be /etc/localtime

Then find other files with the same contents:

[root@server ~]# find /usr/share/zoneinfo/ -type f -exec md5sum {} \; | grep fcccbcf95c718cf2fdee557763e460be
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/GMT+0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/GMT0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/Greenwich
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/GMT
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/GMT+0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/GMT0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/Greenwich
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/GMT
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/Etc/GMT+0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/Etc/GMT0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/Etc/Greenwich
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/Etc/GMT
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/Etc/GMT-0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/posix/GMT-0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/Etc/GMT+0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/Etc/GMT0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/Etc/Greenwich
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/Etc/GMT
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/Etc/GMT-0
fcccbcf95c718cf2fdee557763e460be /usr/share/zoneinfo/GMT-0

So it looks like this server's /etc/localtime has the same contents as all the GMT files. This box is in GMT.