Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Stairway to Data, Step 4: Temporal Data Expand / Collapse
Author
Message
Posted Thursday, May 12, 2011 10:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 1,945, Visits: 3,025
Comments posted to this topic are about the item Stairway to Data, Step 4: Temporal Data

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1108186
Posted Thursday, May 12, 2011 10:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 5, 2011 10:54 PM
Points: 2, Visits: 12
Is anyone else having browser rendering problems with this page. In IE8 I am getting large areas of white space after each section of <pre class="inline"> ... </pre>
Post #1108192
Posted Tuesday, June 14, 2011 11:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 10:47 AM
Points: 77, Visits: 206
set your IE8's Compatibility View. it worked for me :)
Post #1125459
Posted Wednesday, June 15, 2011 7:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 4,368, Visits: 6,208
1) by using the date datatype you are limiting the calendar table to SQL 2008+. Most installations out there are still 2005-.

2) fiscal_year INTEGER NOT NULL,
fiscal_month INTEGER NOT NULL,
week_in_year INTEGER NOT NULL, -- SQL Server is not ISO standard
holiday_type INTEGER NOT NULL

That is a massively bloated data structure. Do you really need +-2.1BILLION values for each of those? fiscal year can certainly be a smallint and the others tinyints, for a savings of 11 bytes per row with zero lost functionality.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1125688
Posted Wednesday, June 15, 2011 7:37 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 7:38 AM
Points: 384, Visits: 432
Joe, thanks for laying it out like this. It’s interesting how some of these concepts are so common, except when you have to actually explain them.

I’ve been doing data warehousing / OLAP work for a few years now and always run into date and time requirements that most management just blows off (handle it, don’t bother me with the details).

When dealing with a time / duration requirement I always end up using 2 columns (From_UTC, To_UTC) of a DATETIME data type, or more recently DATETIMEOFFSET. To display the duration I end up subtracting them (To_UTC – From_UTC), and formatting the result to HH:MM:SS. Not exactly the best, but it works.


Beer's Law: Absolutum obsoletum
"if it works it's out-of-date"
Post #1125707
Posted Wednesday, June 15, 2011 7:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 1,677, Visits: 4,785
The worst relational database anti-pattern ever:
containing date/time values a varchar datatype.

Post #1125718
Posted Wednesday, June 15, 2011 9:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 1,945, Visits: 3,025
Kevin:
Having pre-2005 T-SQL is awful for another reason. You have to write CHECK() constriants and do extra temporal math to set the time to 00:00:00 hrs to get a fake DATE data type. I guess every product has its idioms, but this has been a real pain for decades.

And I agree about using SMALLINT (TINYINT is proprietary, so I am agianst it automaticlaly)

Eric:
The temporal data in strings thing goes back to COBOL, but it was encouraged in the early Sybase/SQL Server days by the CONVERT() function. This was the only way we thoguht about them! The second you see a CONVERT() you know you have a 1950's COBOLO programmer, even if he does not know it himself.

Oracle used to have a way to insert invalidate dates(i.e. '2011-02-31' and worse) into a column. Once garbage was in the table, you could not get it out with straight SQL; you had to use a cursor.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1125835
Posted Wednesday, June 15, 2011 2:25 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, September 27, 2014 9:44 PM
Points: 831, Visits: 1,583
A continuum is finitely divisible,


Is this a typo or am I not getting something fairly fundamental? A continuum is INfinitely divisible surely. I would have thought that we humans make choices about the divisibility of the time continuum that we can live with and pick our software and data types to suit those choices. Which brings me to a pet gripe of mine in the way people write SQL (or TSQL anyway) to extract activity between two points in time.

Say your boss comes to you and says "I need a count of all the events that occurred between 1 June 2010 and 30 June 2010." Say also you are querying data where the data type is datetime. The most obvious mistake will be:
WHERE my_event_start BETWEEN '20100601' and '20100630'

...because this eliminates activity from 30 June except that which started precisely at 20100630 00.00.00.000. So is the solution to say:
WHERE my_event_start BETWEEN '20100601' and '20100701'

?
No, because any activity where the clerk forgot to enter the time component for activity on 20100701 will be counted in your June data. So is the solution to say:
WHERE my_event_start BETWEEN '20100601' and '20100630 23:59:59'

?
Still no. Datetime is more precise (divisible) than the units you've used. Any activity between 23:59:59 and 20100701 00.00.00.000 won't be picked up. So is the solution to say:
WHERE my_event_start BETWEEN '20100601' and '20100630 23:59:59.999'

?
Well frankly, no again. TSQL can do weird rounding things to milliseconds because it's granularity is coarser than the millisecond. I'm pretty sure .999 will be rounded to the next second which is 20100701 00.00.000. So what's the answer?

Don't use BETWEEN when dealing with datetimes. Use:
WHERE my_event_start &gt;= '20100601' and my_event_start &lt; '20100701'

Edit question: The above should say ">=" and "<". How do I make the IFcode shortcuts cooperate?




One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1126098
Posted Wednesday, June 15, 2011 3:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:50 PM
Points: 292, Visits: 1,623
It's nice to see someone talking about temporal data. I do a lot of data warehousing and it's pretty surprising how many people don't understand temporal data. Since the majority of database systems are relational, I'd suggest anyone interested in temporal data read "Temporal Data & the Relational Model." It's a bit academic, but it'll give you the framework for implementing a temporal database (just be sure to come up with your own implementation ).

Now if only MS would include a temporal data type so we don't have to write so much code to enforce proper temporal constraints.... dare to dream...
Post #1126148
Posted Wednesday, June 15, 2011 3:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:48 PM
Points: 3, Visits: 62
Side note: This may seem critical, but how about checking the spelling and grammar of an article before publishing it? It's a great article, but has several grammatical errors in it that really detract from the way the article reads and flows. Why present an interesting and thoughtful article in a sloppy manner?
Post #1126151
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse