Ordering Dates in Queries

Databases
1

I’m learning how to work with dates stored in database tables, and it’s cool that there are so many things you can do with them, but the sheer number of options makes it really confusing. I’m also running into all kinds of quirks; the latest is the error message “out of range value” for years like 1600.

So I’m thinking of a new scheme. First, my page URL’s look like this: MySite/Calendar/January_1, or MySite/Calendar/July_4

So rather than mess with numerical date/datetime fields, I want to create a simple char field (URL) with the appropriate values (e.g. July_4). Then, to avoid problems with years (like “out of range value”), I want to change my year field to an integer field. It would look like this:

Date (char) | Year (int)
July_4 | 1966
July_4 | 1970
July_23 | 1432

But I want to be able to order by date, also - like this:

Jan. 1, 1329
Jan. 1, 1948
Jan. 1, 2010
Jan. 2, 1946
Feb. 7, 1940
Oct. 11, 1984

So I want to create a date or datetime field that I cite in an ORDER BY query clause. Which would be the best choice (date or datetime), and - if I name the field DateOrder - would I just order it by adding the clause “ORDER BY DateOrder”?

I’m assuming the final DB values would look like this:

July_4 | 1966 | 1966-07-04 (date field)
July_4 | 1966 | 1966-07-04 00-00-00 (datetime field)

And if I’m dealing with a situation where I know the year but not the date, it would look like this:

Ocober_10 | 1966 | 1966-00-00 (date field)
October_10 | 1966 | 1966-00-00 00-00-00 (datetime field)

Thanks.

2

You’ve not said what database server software you’re using but whichever it is, you’re far better off using a datetime field (or whatever it’s called in database server software other then MySQL). All database server software will have functions that you can use in a query for working with dates and times

3

you can’t be using mysql, then, because mysql’s supported date range is ‘1000-01-01’ to ‘9999-12-31’

too bad you’re not using mysql, because mysql actually supports using 00 in the day or month and day portions (unless you turn it off using NO_ZERO_IN_DATE sql mode)

4

Oops, sorry - I should have said I’m using PHP and MySQL. I was confused because I had an error message “out of range” or something like that, and I read somewhere that MySQL only supports dates to 1901. On second thought, that was probably referring to the field type “year,” though, not date or datetime.

It’s cool that are there are so many options for working with dates and time. The flip side is there are so many options it gets confusing - date vs datetime vs year, PHP vs MySQL, etc.

5

Sounds like a Unix timestamp issue
http://www.php.net/time

int time ( void )

Returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

So, is this a Database or PHP problem? i.e. should I move this to the PHP forum?

6

Thanks for making my point. :wink:

I need to read up on Timestamp. I’m not really focusing on time - yet. However, I may eventually display the current time on my web pages. Also, I eventually need to set it up so that dates are current from the visitor’s point of view. So if someone is viewing my site in Transylvania, the date at the top of the page will switch to July 1 only when it’s July 1 in Transylvania.

It will take me a while to wade through all this time stuff…

7

I think if you follow r937’s lead, if you use MySQL’s date in combination with PHP to offset timezone differences you’ll be fine.

As far as the “circa” (in another of your threads) another field or table should be able to let you do that.

What does your table schema look like? (the CREATE)

8

Sorry, I couldn’t figure out “CREATE.” I typed DESCRIBE people_bios into phpMyAdmin > SQL. I pasted the output at the end of this post. However, these are the only rows that relate to dates:

Edit	Delete	Born	date	YES	 	NULL	 
Edit	Delete	Born2	varchar(255)	YES	 	NULL
Edit	Delete	Died	date	YES	 	NULL	 
Edit	Delete	Died2	varchar(255)	YES	 	NULL

When I’m not sure when a person was born or died, I use the field Born2/Died2 to explain the situation.

The table people_bios generally lists basic biographical information for people - First, Middle and Last names (along with prefixes and suffixes), along with the date and place of birth and death.

N mediumint(4) NO PRI NULL auto_increment
Edit Delete URL varchar(75) NO MUL
Edit Delete Common varchar(50) NO NULL
Edit Delete Prefix char(5) YES NULL
Edit Delete First char(25) NO NULL
Edit Delete Middle char(25) YES NULL
Edit Delete Last char(25) NO NULL
Edit Delete Suffix char(5) YES NULL
Edit Delete Alpha varchar(75) NO NULL

Edit	Delete	Born	date	YES	 	NULL	 
Edit	Delete	Born2	varchar(255)	YES	 	NULL	 

Edit	Delete	Birth_Place1	varchar(75)	YES	 	NULL	 
Edit	Delete	Birth_Place2	varchar(50)	YES	 	NULL	 
Edit	Delete	Birth_ID	char(5)	YES	 	NULL	 

Edit	Delete	Died	date	YES	 	NULL	 
Edit	Delete	Died2	varchar(255)	YES	 	NULL	 

Edit	Delete	Death_Place1	varchar(75)	YES	 	NULL	 
Edit	Delete	Death_Place2	varchar(50)	YES	 	NULL	 
Edit	Delete	Death_ID	char(5)	YES	 	NULL	 
Edit	Delete	Image	tinyint(1)	YES	 	NULL
9

please run this instead, it’s much easier to read the results –

SHOW CREATE TABLE people_bios
10

OK, those look like date fields (as best I can tell, not so easy to read)

Got a code example of a query that’s failing?

11

Thanks, Rudy; that’s what I was trying to earlier, but I spaced out the correct way to do it.

Mittineague - I don’t have any failing queries right now. I was just looking for suggestions regarding a date or datetime field I could use in an ORDER BY clause. I was also confused by dates with missing months or days (00).

I posted my table structure below, but here are the most relevant lines:

Born date DEFAULT NULL,
Born2 varchar(255) COLLATE latin1_general_ci DEFAULT NULL,

CREATE TABLE people_bios (
N mediumint(4) NOT NULL AUTO_INCREMENT,
URL varchar(75) COLLATE latin1_general_ci NOT NULL DEFAULT ‘’,
Common varchar(50) COLLATE latin1_general_ci NOT NULL,
Prefix char(5) COLLATE latin1_general_ci DEFAULT NULL,
First char(25) COLLATE latin1_general_ci NOT NULL,
Middle char(25) COLLATE latin1_general_ci DEFAULT NULL,
Last char(25) COLLATE latin1_general_ci NOT NULL,
Suffix char(5) COLLATE latin1_general_ci DEFAULT NULL,
Alpha varchar(75) COLLATE latin1_general_ci NOT NULL,
Born date DEFAULT NULL,
Born2 varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
Birth_Place1 varchar(75) COLLATE latin1_general_ci DEFAULT NULL,
Birth_Place2 varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
Birth_ID char(5) COLLATE latin1_general_ci DEFAULT NULL,
Died date DEFAULT NULL,
Died2 varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
Death_Place1 varchar(75) COLLATE latin1_general_ci DEFAULT NULL,
Death_Place2 varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
Death_ID char(5) COLLATE latin1_general_ci DEFAULT NULL,
Image tinyint(1) DEFAULT NULL,
PRIMARY KEY (N),
KEY URL (URL)
) ENGINE=MyISAM AUTO_INCREMENT=247 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

12

The error message wasn’t as a result of trying a query?

13

Sorry; that wasn’t really in association with this particular thread. It was just a general observation. I had a situation where I was getting “out of range” error messages on a “year” field. I fixed it by changing it to INT. I read somewhere that MySQL doesn’t support years before 1901. I thought they were talking about the date or datetime field, but it was apparently a reference to the year field.

And just a few minutes ago I learned that you can publish interactive Excel tables online - but dates before 1904 won’t work unless you modify it somehow. Like history began at 1905! :wink: