CodeSOD: The Falling Dates

In the US, we "fell back"- that is to say we abandoned Daylight Saving Time and moved back to Standard Time, and I think the general consensus is that time changes are the real WTF.

So let's use this as a moment to look at some bad date handling code, an evergreen topic.

First, we turn to Adam, who's doing some minor work on an old PHP application.

# Yes this is really how you do date constants in PHP now :-(define('XXXCutoffDate', (new DateTime(YEAR . '-01-19 00:00:00', new DateTimeZone('America/Chicago')))->getTimestamp() );

This isn't inherently how you have to do date constants, but it's certainly a way to do date constants. Somehow, despite going the route of using constructors and objects, the original developer still managed to find a way to work string concatenation into the process, making this possibly the most PHP of all PHP code.

From Tomasz, we get another classic anti-pattern.

 public function getEidtTimeRemaining($createDate, $timeout) { $oDb = $this->getDefaultAdapter(); $sql = 'SELECT NOW() AS now'; $row = $oDb->query($sql)->fetch(); $now = strtotime($row['now']); return $timeout - ($now - strtotime($createDate)); }

Yep, the good ol' "run a database query to know what time it is". If only there were some other way to ensure all your front-end servers knew what time it was. No, we must do a database query.

Someone should probably eidt that code.

Finally, one of Noah's predecessors was thinking about datawarehousing. It's frequently common there to generate tables that have thousands of dates as records, so you can easily join against them, making time range queries easy.

So they implemented a table that looked like this:

CREATE TABLE [dbo].[date_dim]( [Date_key] [int] IDENTITY(1,1) NOT NULL, [full_date] [datetime] NOT NULL, [day_of_week] [smallint] NOT NULL, [day_num_in_month] [smallint] NOT NULL, [day_num_overall] [smallint] NOT NULL, [day_name] [varchar](9) NOT NULL, [day_abbrev] [char](3) NOT NULL, [weekday_flag] [char](1) NOT NULL, [week_num_in_year] [smallint] NOT NULL, [week_num_overall] [smallint] NOT NULL, [week_begin_date] [datetime] NOT NULL, [week_begin_date_key] [smallint] NOT NULL, [month] [smallint] NOT NULL, [month_num_overall] [smallint] NOT NULL, [month_name] [varchar](9) NOT NULL, [month_abbrev] [char](3) NOT NULL, [quarter] [smallint] NOT NULL, [year] [smallint] NOT NULL, [yearmo] [int] NOT NULL, [fiscal_month] [smallint] NOT NULL, [fiscal_quarter] [smallint] NOT NULL, [fiscal_year] [int] NULL, [fiscal_period] [char](20) NULL, [last_day_in_month_flag] [char](1) NOT NULL, [same_weekday_year_ago_date] [datetime] NOT NULL, [days_in_month] [numeric](6, 4) NOT NULL, [char_full_date] [char](8) NOT NULL, CONSTRAINT [PK_Dates] PRIMARY KEY CLUSTERED( [Date_key] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

This table had dates from 1900 to 2078 in it, and that's not a terrible idea, honestly. But instead of using it to make date-based queries more efficient, they instead used it to reinvent all of the built-in date functions of SQL Server, like this date diff function.

create FUNCTION [dbo].[cal_day_diff](@lcStartDate CHAR(8),@lcEndDate CHAR(8))RETURNS INTBEGINRETURN (select count(*) from date_dim where char_full_date between @lcStartDate AND @lcEndDate AND day_of_week BETWEEN 1 AND 7)END

Dates, as always, are hard.

[Advertisement] ProGet's got you covered with security and access controls on your NuGet feeds. Learn more.