September 6, 2017 at 4:19 pm
Understood but my big question was on what the start of a month and year should be. For example, should a month start on the first of the month or should it be based on whole weeks for the month like ISO is?
--Jeff Moden
September 7, 2017 at 1:40 pm
jcobb 20350 - Wednesday, August 30, 2017 12:08 PM
The ISO 8601 standards allow YYYYMMDD, as well.
Nope. Look at the ANSI/ISO SQL standards. We only allow the"yyyy-mm-dd" display format, which includes dashes. The dashes are one of the punctuation marks allowed in the Unicode standard. The reason for restricting ourselves to one and only one display format was simplicity. I can be which one of the committee members said it, but "would you mix Roman and Arabic numerals on the clock face?"
There is also ISO 8601 format that allows a "T" separator in timestamps. This has the advantage of getting rid of space in the middle of the field, which conflicts with some other ISO standards. I like this one for that reason, but I was outvoted. Likewise, I like the MySQL zero convention strings "yyyy-mm-00" for a month, and "yyyy-00-00" for the year. These are up for consideration inside ISO, but I don't know what progress they've made.
The advantages are:
1)they don't introduce spaces or variable length strings. Fixed length encodings are highly desirable.
2) They let people designing screens and paper forms know what to do,
3) The length becomes a validation, and it's possible to write regular expressions for the encodings
4) they sort correctly as strings in other languages. Nobody likes to remember most of the commercial programming is still done in COBOL, or in a language that doesn't have a temporal data type.
Again, I don't get a vote. Instead, I have to do my programming with strings and regular expression checks.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 7, 2017 at 1:59 pm
jcelko212 32090 - Tuesday, September 5, 2017 12:00 PMjcobb 20350 - Wednesday, August 30, 2017 12:08 PM
The ISO 8601 standards allow YYYYMMDD, as well.
Nope. Look at the ANSI/ISO SQL standards. We only allow the"yyyy-mm-dd" display format, which includes dashes.
Again, I don't get a vote. Instead, I have to do my programming with strings and regular expression checks.
I too thought ISO allowed YYYYMMDD. For example:
NASA.gov/iso-time
"
The hyphens can be omitted if compactness of the representation is more important than human readability, for example as in
19950204
"
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 7, 2017 at 2:11 pm
Jeff Moden - Tuesday, September 5, 2017 1:41 PMYYYY-MM-00????? Is that a joke? :angry::crazy::alien::sick:
Nope. ANSI/ISO standard SQL has interval data types, which are part of the temporal model. The bad news is we didn't have any display format for them, separate from the date ("yyyy-mm-dd"), time ("HH:MM:SS.ssss") and timestamp ("yyyy-mm-dd HH:MM:SS.ssss") datatypes
I like the MySQL zero convention strings "yyyy-mm-00" for a month, and "yyyy-00-00" for the year. These are up for consideration inside ISO, but I don't know what progress they've made. MySQL has large, well-known community and therefore some influence.
The advantages are:
1)they don't introduce spaces or variable length strings. Fixed length encodings are highly desirable.
2) They let people designing screens and paper forms know what to do,
3) The length becomes a validation, and it's possible to write regular expressions for the encodings
4) they sort correctly as strings in other programming languages. Nobody likes to remember most of the commercial programming is still done in COBOL, or in a language that doesn't have a temporal data type.
5) the numeric strings are language independent. This is very important in international standards.
Do youb have a better suggestion? with what suppport?
Please post DDL and follow ANSI/ISO standards when asking for help.
September 7, 2017 at 3:51 pm
Ed Wagner - Tuesday, September 5, 2017 2:16 PMJeff Moden - Tuesday, September 5, 2017 1:41 PMYYYY-MM-00????? Is that a joke? :angry::crazy::alien::sick:
Nope. ANSI/ISO standard SQL has interval data types, which are part of the temporal model. The bad news is we didn't have any display format for them, separate from the date ("yyyy-mm-dd"), time ("HH:MM:SS.ssss") and timestamp ("yyyy-mm-dd HH:MM:SS.ssss") datatypes
I like the MySQL zero convention strings "yyyy-mm-00" for a month, and "yyyy-00-00" for the year. These are up for consideration inside ISO, but I don't know what progress they've made. MySQL has large, well-known community and therefore some influence.
The advantages are:
1)they don't introduce spaces or variable length strings. Fixed length encodings are highly desirable.
2) They let people designing screens and paper forms know what to do,
3) The length becomes a validation, and it's possible to write regular expressions for the encodings
4) they sort correctly as strings in other programming languages. Nobody likes to remember most of the commercial programming is still done in COBOL, or in a language that doesn't have a temporal data type.
5) the numeric strings are language independent. This is very important in international standards.
Do youb have a better suggestion? with what suppport?
You, advocating for an ENGINE SPECIFIC feature when you constantly bash those of us who write code to the engine to get the best performance and scalability we can.
Just. Go. Away.
September 7, 2017 at 4:07 pm
Jeff Moden - Wednesday, September 6, 2017 4:19 PMUnderstood but my big question was on what the start of a month and year should be. For example, should a month start on the first of the month or should it be based on whole weeks for the month like ISO is?
Whole weeks.
September 7, 2017 at 5:53 pm
Ed Wagner - Tuesday, September 5, 2017 2:16 PMJeff Moden - Tuesday, September 5, 2017 1:41 PMYYYY-MM-00????? Is that a joke? :angry::crazy::alien::sick:
Nope. ANSI/ISO standard SQL has interval data types, which are part of the temporal model. The bad news is we didn't have any display format for them, separate from the date ("yyyy-mm-dd"), time ("HH:MM:SS.ssss") and timestamp ("yyyy-mm-dd HH:MM:SS.ssss") datatypes
I like the MySQL zero convention strings "yyyy-mm-00" for a month, and "yyyy-00-00" for the year. These are up for consideration inside ISO, but I don't know what progress they've made. MySQL has large, well-known community and therefore some influence.
The advantages are:
1)they don't introduce spaces or variable length strings. Fixed length encodings are highly desirable.
2) They let people designing screens and paper forms know what to do,
3) The length becomes a validation, and it's possible to write regular expressions for the encodings
4) they sort correctly as strings in other programming languages. Nobody likes to remember most of the commercial programming is still done in COBOL, or in a language that doesn't have a temporal data type.
5) the numeric strings are language independent. This is very important in international standards.
Do youb have a better suggestion? with what suppport?
I know exactly what "progress" they've made in that area because I've downloaded the late (December, IIRC) 2016 draft of the 8601 standard. None of what you suggest for "00" date parts appears in the draft. They have left in the YYYY-MM and MM-DD notations. From what I can see, they've done nothing to change the currently miserable interval notations, either, but at least they've not made it worse.
As for the MySQL "standard" of YYYY-MM-00 and YYYY-00-00, I do agree with all of the advantages you've listed with one grand exception...
You're not supposed to store date formatted as text strings or integers in databases except for display purposes when used in calendar or reporting tables in lieu of a GUI or reporting application. The reasons for not storing such formatted temporal representations should be obvious to someone like you (especially with how much you degenerate the quality of your posts when others do it 😉 )... you have to do conversions get date into that shape and you have to do conversions if you want to do any type of date math with that shape and, according to you, you should keep the presentation layer and data layers in two totally separated worlds. It also takes 10 bytes instead of either 4 or 8 for storage purposes.
It's also proprietary to MySQL and you shouldn't forget the absolute brow beating you give anyone and everyone for using ANYTHING that's proprietary/Non-ANSI/Non-ISO in ANY RDBMS or the SQL Language in general. In fact, you regularly chew the face off of people that do any kind of string display of dates, never mind actually storing them in a database.
--Jeff Moden
September 8, 2017 at 8:32 am
Ed Wagner - Tuesday, September 5, 2017 2:16 PMJeff Moden - Tuesday, September 5, 2017 1:41 PMYYYY-MM-00????? Is that a joke? :angry::crazy::alien::sick:
Nope. ANSI/ISO standard SQL has interval data types, which are part of the temporal model. The bad news is we didn't have any display format for them, separate from the date ("yyyy-mm-dd"), time ("HH:MM:SS.ssss") and timestamp ("yyyy-mm-dd HH:MM:SS.ssss") datatypes
I like the MySQL zero convention strings "yyyy-mm-00" for a month, and "yyyy-00-00" for the year. These are up for consideration inside ISO, but I don't know what progress they've made. MySQL has large, well-known community and therefore some influence.
The advantages are:
1)they don't introduce spaces or variable length strings. Fixed length encodings are highly desirable.
2) They let people designing screens and paper forms know what to do,
3) The length becomes a validation, and it's possible to write regular expressions for the encodings
4) they sort correctly as strings in other programming languages. Nobody likes to remember most of the commercial programming is still done in COBOL, or in a language that doesn't have a temporal data type.
5) the numeric strings are language independent. This is very important in international standards.
Do youb have a better suggestion? with what suppport?
Well, it sounds like a joke...and a very bad one at that. Don't get me wrong - the YYYYMMDD is fine if it's an integer. It's fixed-width, allows efficient sorting and consumes 4 bytes. I won't get into NCIs on them because you've completely ignored the concept in previous posts.
Personally, I'll stick with using datetime to store datetime values. There's this concept of a domain that limits the values that can be stored that's very appealing to most of us. For example, a column that stores a cost should be a numeric data type and shouldn't allow "B4.XZ" as a value. By the same token, a datetime shouldn't be a string of any type, because it could contain "20$7STFU". Yes, you could put a constraint on your string column, but that's a hack when the appropriate data type already exists. In that concept of a domain, the value "20170200" is an invalid date. Perhaps it's my limited imagination, but I can see no point in time that occurs on day zero of any week, month or year. For more information on domains, see https://en.wikipedia.org/wiki/Data_domain.
I've read https://www.iso.org/iso-8601-date-and-time-format.html and could find nothing in the description of YYYY-MM-DD that allows for 00 as a day. Quite honestly, the internal storage of a datetime is irrelevant to the conversation of data exchange because people don't exchange the internal storage. They exchange the data in a standard way, which is YYYY-MM-DD. I would never accept data with day 00. Out of curiosity, if 00 is a valid day, does that mean that -2 is okay as well?
I'm not going to get into a quote-fest of the ISO standards with you (or anyone else) because you obviously know them far better than I ever will...or care to. I find them to be overly-politicized and don't have the inclination or the time to read them all. I can hardly believe that you, of all people, is advocating for storing dates as strings to allow 00 as a day. It's as though you're flipping off the the very ISO standards you hold so dear.
September 8, 2017 at 9:12 am
jcelko212 32090 - Thursday, September 7, 2017 2:11 PMI like the MySQL zero convention strings "yyyy-mm-00" for a month, and "yyyy-00-00" for the year. These are up for consideration inside ISO, but I don't know what progress they've made. MySQL has large, well-known community and therefore some influence.
You are a hypocrite. You regularly browbeat people for using proprietary features and then turn right around and promote a proprietary feature that is not even available in the dialect that this site is dedicated to.
This site is dedicated to Microsoft SQL Server. If you want to promote MySQL, try a site dedicated to MySQL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 8, 2017 at 9:30 am
jcobb 20350 - Thursday, September 7, 2017 4:07 PMJeff Moden - Wednesday, September 6, 2017 4:19 PMUnderstood but my big question was on what the start of a month and year should be. For example, should a month start on the first of the month or should it be based on whole weeks for the month like ISO is?Whole weeks.
Just to reassure, I think I've sussed this problem for you. I'll post tonight after work.
--Jeff Moden
September 8, 2017 at 11:30 am
Jeff Moden - Thursday, September 7, 2017 5:53 PMIt's also proprietary to MySQL and you shouldn't forget the absolute brow beating you give anyone and everyone for using ANYTHING that's proprietary/Non-ANSI/Non-ISO in ANY RDBMS or the SQL Language in general. In fact, you regularly chew the face off of people that do any kind of string display of dates, never mind actually storing them in a database.
drew.allen - Friday, September 8, 2017 9:12 AMYou are a hypocrite. You regularly browbeat people for using proprietary features and then turn right around and promote a proprietary feature that is not even available in the dialect that this site is dedicated to.This site is dedicated to Microsoft SQL Server. If you want to promote MySQL, try a site dedicated to MySQL.
Thank you both. Ten internet points for each of you.
September 8, 2017 at 2:23 pm
I have to tell you horrible secret about how standards are made. In my day, we went to the bar. The hotel where ANSI X3H2 was meeting, brought are very primitive laptops and proceeded to ignore the company rules about internal research as trade secrets. Today, I gather, things are being done on the Internet, rather than over beer (damn I miss the beer!). But because of the Internet, what would have been personal conversations decades ago when I was in the standards business, is now limited to personal emails. You don't want your boss/organization/whatever seeing what you've done until you have really formalized it. Somebody will send a private email from company X lab to company Y lab, and ask if it seems like a good idea. And if we can agree on syntax and semantics before we submit it to our standards body. This is how the Windows clause (OVER) got introduced into the SQL standards.
>> From what I can see, they've done nothing to change the currently miserable interval notations, either, but at least they've not made it worse. <<
There is a rule about standards is "be glad that it didn't worse" :-). Too often they become insanely detailed; Dave McGovern had a wonderful quote "a committee never met a feature it didn't like!" He was on the mark.
>> As for the MySQL "standard" of YYYY-MM-00 and YYYY-00-00, I do agree with all of the advantages you've listed with one grand exception...
You're not supposed to store date formatted as text strings or integers in databases except for display purposes when used in calendar or reporting tables in lieu of a GUI or reporting application. <<
I think you missed the point. Those were display formats, not internal storage models. For example, DB2 used to do dates as character strings because it was coming from a world of COBOL (with decades of handling those strings). SQL Server was coming from a world of UNIX, so it's temporal stuff was binary. I honestly do not know about the internals of the MySQL representations (and I did a series of online presentations for them, years ago).
>> you should keep the presentation layer and data layers in two totally separated worlds. It also takes 10 bytes instead of either 4 or 8 for storage purposes. <<
I agree! And I don't care about the number of bytes. That's physical; all I do is logical.
>> It's also proprietary to MySQL and you shouldn't forget the absolute brow beating you give anyone and everyone for using ANYTHING that's proprietary/Non-ANSI/Non-ISO in ANY RDBMS or the SQL Language in general. <<
MySQL started off as an open source database and there's an open source version of it now. Buy me a scotch, and I'll tell you what they think of the "Evil of Oracle". But this kind of formatting can't be proprietary. I have a hierarchy a standardization, which starts at the ISO level, and goes down to national standards (ANSI, DIN, etc.), industry standards and finishes off at the bottom with open source. I don't like any proprietary encodings, and finally "local cowboy crap" where someone just invented their own stuff on the fly in one program. No research, no planning and even worse, perhaps a sequential identity property numbering of the values.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 8, 2017 at 8:59 pm
Jeff Moden - Tuesday, September 5, 2017 1:41 PMjcelko212 32090 - Tuesday, September 5, 2017 12:00 PMjcobb 20350 - Wednesday, August 30, 2017 12:08 PM
The ISO 8601 standards allow YYYYMMDD, as well.
Nope. Look at the ANSI/ISO SQL standards. We only allow the"yyyy-mm-dd" display format, which includes dashes.
Again, I don't get a vote. Instead, I have to do my programming with strings and regular expression checks.
I too thought ISO allowed YYYYMMDD. For example:
NASA.gov/iso-time
"
The hyphens can be omitted if compactness of the representation is more important than human readability, for example as in
19950204
"
Celko is incorrect. I won't quote from the most recent draft of the standards because it's supposedly not yet been released but I can quote from ISO 8601:2004(E) which clearly states on page #13...
4.1.2.2 Complete representations
When the application identifies the need for a complete representation of a calendar date, it shall be one of the numeric expressions as follows, where [YYYY] represents a calendar year, [MM] the ordinal number of a calendar month within the calendar year, and [DD] the ordinal number of a calendar day within the calendar month.
Basic format: YYYYMMDD Example: 19850412
Extended format: YYYY-MM-DD Example: 1985-04-12
--Jeff Moden
September 8, 2017 at 9:56 pm
jcelko212 32090 - Friday, September 8, 2017 2:23 PMI think you missed the point. Those were display formats, not internal storage models.
First of all, in all the posts of yours that I've seen on that subject, you've never stated that. Your posts have been a part of the reason why I've had to reconfirm with a couple of clients that you should never store dates in such a format.
Second, you've never stated that the YYYY-MM-00 and YYY-00-00 formats should only be generated by the front end. You keep saying that it's a feature (and a bloody proprietary one at that) of MySQL, which is a database engine. And yet, you keep harping on people that they should not do such formatting via the database engine. As someone else said, that makes you a hypocrite on the subject and you need to stop ripping people a new one when they want to format a date for display using T-SQL.
You've even been hypocritical in the very post I extracted the quote above from. In response to my statement (within the >> << marks), you said you agree ...
jcelko212 32090 - Friday, September 8, 2017 2:23 PM>> you should keep the presentation layer and data layers in two totally separated worlds. It also takes 10 bytes instead of either 4 or 8 for storage purposes. <<
I agree!
... and yet you continue to "like" using the proprietary (which you hate for everything in T-SQL) MySQL format of YYYY-MM-00 and YYYY-00-00 formats which, by their very nature, is a violation of the rules that you pound people with about keeping presentation layer things out of the data layer.
jcelko212 32090 - Friday, September 8, 2017 2:23 PM
And I don't care about the number of bytes. That's physical; all I do is logical.
I know a whole lot of people that would argue with you on the last 5 words of that quote but it does explain a whole lot when it comes to your take on databases and writing code against them not to mention your rather narrow take on the likes of auto-numbering IDENTITY columns. Logical design is the tip of the iceberg especially when it comes to processing time, storage requirements, memory requirements, and bytes in the "pipe". Without a proper manifestation in the physical world, you end up with good but very slow and resource intensive code that frequently violates the SLA for user request-to-appearance times. There are, however, two good things about people that skip the physical considerations; 1) There are so many that do so that the engineers of the world have to keep finding ways to make the physical world faster and larger and that's a good thing. 2) It provides many people with jobs to fix code that has only been logically designed.
--Jeff Moden
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply