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`

Mock time with Test::Time for Perl

Voila:

use Test::Time time => 1;

my $now = time;    # $now is equal to 1
sleep 300;         # returns immediately, displaying a note
my $then = time;   # $then equals to 301

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*

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;
}

How to capture linux "time" output

Pipe time output into a file like this:

{ time ls; } 2>time.output

Don't forget the semicolon!

(source)

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

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 );

Perl: convert a number of seconds into days, hours, mins, secs

printf("That took %d"."d %d"."h %d"."m %d"."s",(gmtime($seconds))[7,2,1,0])); 

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.