News database updated multiple times throughout each day. The database has a date field and time field, when sort on date is used it randomly rearranges the post time.

Do I have to modify the $get_unix_date subroutine or is there a way that I can combine date and time into one field?

I am probably missing the obvious, I have been working non-stop on DB Man for the last three days.

Also, is it possible to search for just the MONTH in a date field? For example , I am having problems when I enter the key word May in search all fields, hit submit and it fills in all search fields with may but doesn’t search the database and gives no error. For the time being I just switched the date field type to ALPHA and that solved the search problem. Not a long term solution!

Any help on how to setup this sort will be massively appreciated.

Cheers!

------------------
Larry "NgtCrwlr" Mingus
www.makeitsimple.com

 
and set your default value to

&unix_time

Again, no quotes around it.

Then, sort on this field. (Set the data type of this field to "numer.")


(I broke up the line a little bit so it wouldn't mess up the page here in the forum. You should be able to find it, though.)

Change it to


It won't check to see if the date is in the right format then, so you won't get an "invalid date format" message.


------------------
JPD

JPDeni, you-da-MAN!
Thanks again for the wickedly fast reply.

I am going to start working on your suggestions now, I will let you know how it works out.

I must have something screwed up in the search routine. When you get a chance take a look at this link:
http://www.makeitsimple.com/...w_records=1&ID=*
First do a keyword search using Abit as the keyword. Then try May as the keyword and you will see first hand what is happening. I just found out that it doesn't matter if the keyword is May, I accidentally typed Many and it does the same thing.

------------------
Larry "NgtCrwlr" Mingus
www.makeitsimple.com

Well, there is sort of an error message -- "No matching records"

Your keyword search doesn't work for "May" because the keyword is not applied to fields whose data type is "date." I didn't know that before, but I just looked at the script. You could change that if you wanted to.



------------------
JPD

LOL, I guess I was so overtaken by May being put into all of the feild boxes that I missed the obvious Duh!

By the way, SORRY about the last comment You-Da-man! It's that dam sterio typical man thing, always thinking like a man. I should have thought about the possibility that you were not a man. I hope I didn't offend you.

Thanks Carol

------------------
Larry "NgtCrwlr" Mingus
www.makeitsimple.com

Not at all. I sorta chose to use this nickname because it's "gender neutral." I expected that some would assume I was a man. Besides, my radical feminism has mellowed a bit over the years.

Let me know if you want to include the date in a keyword search. There's just a couple of lines that you'd need to take out.




------------------
JPD

Whew! I feel better now. Thanks Carol

Okay, I'm going to run this by you again just in case you want ponder it while I do some testing on the unix_date routine.

I want to build a database from my old data all the way back to Jan 1, 1999. The problem is I already have data for May entered and am adding new data daily.

I need to figure out how I can add old data on top of new and not have it show up as a new post.

It would not be a problem if all the records were entered sequentially old to new, the record(ID) field would sort everything fine.

I need to come up with an editable AND sort-able date/time field.

I am going to stop here and go play with the Unix date that you suggested and see if I can get that to work. If I can create an editable Unix date/time field I think the problem will be solved and I will be in cat's heaven.

------------------
Larry "NgtCrwlr" Mingus
www.makeitsimple.com

I see your problem. I think we can come up with an editable and sortable date/time field. It will take some doing, though.

Replace sub get_date with the following:


That will give you the date and time in the format 11-May-1999 22:44:07. If you need "am" and "pm," we can do that, too. But I'd rather not go through the ponder time if you don't need it.

You'll also need to be able to convert the date/time back to unix time.

Replace sub date_to_unix with the following


One thing to be careful about. The forum sticks a space between two | characters, which should not be there. In the first routine above, there is | |. If you copy and paste the code, be sure to take out the space between the two characters. Otherwise you'll get an error.

Oh, one other thing. When you are entering the old data, you don't need to worry about entering a time if you don't want to. The date_to_unix routine will work fine with just a date, as long as it's in the correct format.


------------------
JPD







[This message has been edited by JPDeni (edited May 13, 1999).]

All I can say is AWESOME! I have goose bumps of excitement. Really.

I gave up on the date problem a few hours ago thinking that maybe later my brain would engage later. Hah! I started to work on seting up the game_news database. So this is fantastic news. Now I can try incorporating the changes on a database that isn't in use.

Man I can't tell you how happy I am... or should I say, how happy I will be.

I am going to start working on it now, I'll let you know how it goes.

Call me happy man! Thank you! Thank you!

------------------
Larry "NgtCrwlr" Mingus
www.makeitsimple.com

I must admit that my helping folks here isn't completely altruistic. I do get a charge out of the "thank you"s.

Also, as I was posting the code above, I realized how much I'd learned. It wasn't that long ago that I looked at the date_to_unix subroutine and had no idea how it worked, much less how to change it. I know that much of my knowledge has come from answering questions here on the forum. It keeps my brain going.

Please let us know when you get it all finished. I'd love to see it.


------------------
JPD

The subs are in and they test out fine. Now, I am going to start entering data so I can check the sort function.

If all goes well I should have an SSI call for the game_news database (with data) incorporated into the front page by daybreak. I will give you a shout when I have something worthwhile to look at.

Many many... Thank you's!

------------------
Larry "NgtCrwlr" Mingus
www.makeitsimple.com

I'm mixing and matching.... I'm using the universal date translator and trying to create fields that contain BOTH date and time for sorting. Here is the code I have...

sub date_to_unix {
# --------------------------------------------------------
# This routine takes dates in almost any format and returns the time a la UNIX time().
# Some things to be careful about..
# int your values just in case to remove spaces, etc.
# catch the fatal error timelocal will generate if you have a bad date..
# Unlike the original subroutine, months are indexed from 1, not 0.
# my ($date) = $_[0];
my ($date_and_time) = $_[0];
my ($date, $time1) = split(/ /, $_[0]);
print "Content-type: text/html\n\n";
$html_headers_printed = 1;
print qq|date $date time $time1<br>|;
my ($hour, $min, $sec) = split(/:/, $time1);
# this is used to decode human date.
my (%months) = ("jan" => 1, "feb" => 2, "mar" => 3, "apr" => 4, "may" => 5, "jun" => 6,
"jul" => 7, "aug" => 8, "sep" => 9, "oct" => 10, "nov" => 11,"dec" => 12,
"january" => 1, "february" => 2, "march" => 3, "april" => 4, "june" => 6,
"july" => 7, "august" => 8, "september" => 9, "october" => 10, "november" => 11,
"december" => 12, "sept" => 9);
# this is used to generate unix date
my (%months1) = ("Jan" => 0, "Feb" => 1, "Mar" => 2, "Apr" => 3, "May" => 4, "Jun" => 5, "Jul" => 6, "Aug" => 7, "Sep" => 8, "Oct" => 9, "Nov" => 10,"Dec" => 11);
my ($time);
$date =~ s/,|\.//g; # delete any commas or periods that might be in there
$date =~ s/\W/-/g; # change any separators into hyphens
if ((defined($months{lc(substr($date,2,3))})) && (length($date) == 9)) { # date format ddMmmyyyy
$day = substr($date,0,2);
$month = $months{lc(substr($date,2,3))};
$year = substr($date,-4);
}
elsif ((defined($months{lc(substr($date,1,3))})) && (length($date) == 8)) { # date format dMmmyyyy
$day = substr($date,0,1);
$month = $months{lc(substr($date,1,3))};
$year = substr($date,-4);
}
elsif ((substr($date,4,2) > 12) && (length($date) == 8)) { # date format ddmmyyyy
$year = substr($date,4,4);
$month = substr($date,2,2);
$day = substr($date,0,2);
}
elsif (($date > 19000000) && (length($date) == 8)) { # date format yyyymmdd
$year = substr($date,0,4);
$month = substr($date,4,2);
$day = substr($date,6,2);
}
else {
$date = lc($date);
@date_part = split /-/,$date;
$year = $date_part[2];
$year = int($year);
if ($year<100) {
if ($year<20) {
$year += 2000;
}
else {
$year += 1900;
}
}
if (defined($months{$date_part[1]})) { #date format dd-Mmm-yyyy
$day = $date_part[0];
$month = $months{$date_part[1]};
}
elsif (defined($months{$date_part[0]})) { #date format Mmm-dd-yyyy
$day = $date_part[1];
$month = $months{$date_part[0]};
}
elsif ($date_part[0] > 12) { #date format dd-mm-yyyy
$day = $date_part[0];
$month=$date_part[1];
}
elsif ($date_part[1] > 12) { #date format mm-dd-yyyy
$day = $date_part[1];
$month=$date_part[0];
}
elsif ($american_dates) { #ambiguous date -- American format
$day = int($date_part[1]);
$month=int($date_part[0]);
}
else { #ambiguous date -- the rest of the world
$day = int($date_part[0]);
$month=int($date_part[1]);
}
}
unless ($day and $month and $year) { return undef; }

use Time::Local;
eval {
$day = int($day); $year = int($year) - 1900;
$time = timelocal($sec,$min,$hour,$day, $months1{$mon}, $year);
};
if ($@) { return undef; } # Could return 0 if you want.
print qq|unix time $time<br>|;
return ($time);

This sub returns the correct unix date when I supply it a date from a search form such as 12/09/06 23:07:07. It returns 1136866027 which is correct UTC time given my time zone. No problem. The problem I have is that when the search form returns with "no records found", the date field has 60--1136 instead of my original date that I entered. I can see that it's scavenging the 60 from the 7th and 8th characters of unix time and the 1136 from the first 4 chars. The month is missing. What do I need to do to fix this? Is it get_computed_date? I am cautious about changing that because I don't want to break anything else. I've just copied the entire new get_date routine code into get_computed_date and got the result I wanted... Now to test to see if I actually broke anything.

Thanks
Wes

I didn't intend it to deal with time. It's really just for dates. You'd probably be better off going back to the original functions.


JPD
----------------------------------------------------
JPDeni's DBMan-ual
How to ask questions the smart way.

Well, I've done some testing and found a bug in the new date_to_unix routine... The $time=timelocal at the end was using the month array and that always gave a null result (duh once I thought about it). It's fixed... Another bug was that records which had been added prior to this fancy date/time format that had no time didn't sort in the right order. The solution for that was to see if the time was null, and if so, assume midnight 00:00:00 for a time. Works like a champ now!!

So here are the three date routines to use with the universal date translator mod that Carol wrote.

BTW, Carol thanks for all the mods you've written and hosted. Alex got this script started (ball rolling) but without you and Lois it would not be nearly as attractive to use. Actually ;-) this script's ease of use is a detriment to me as it has served as a crutch that has prevented me from actually learning MySQL.... sheesh!

Anyway, thanks for all you girls do!
Wes


sub date_to_unix {
# --------------------------------------------------------
# This routine takes dates in almost any format and returns the time a la UNIX time().
# Some things to be careful about..
# int your values just in case to remove spaces, etc.
# catch the fatal error timelocal will generate if you have a bad date..
# Unlike the original subroutine, months are indexed from 1, not 0.
# my ($date) = $_[0];
my ($date_and_time) = $_[0];
my ($date, $time1) = split(/ /, $_[0]);
# if time is null, we provide 00:00:00 as a base time
if (!$time1) {
$time1="00:00:00";
}
my ($hour, $min, $sec) = split(/:/, $time1);
# this is used to decode human date.
my (%months) = ("jan" => 1, "feb" => 2, "mar" => 3, "apr" => 4, "may" => 5, "jun" => 6,
"jul" => 7, "aug" => 8, "sep" => 9, "oct" => 10, "nov" => 11,"dec" => 12,
"january" => 1, "february" => 2, "march" => 3, "april" => 4, "june" => 6,
"july" => 7, "august" => 8, "september" => 9, "october" => 10, "november" => 11,
"december" => 12, "sept" => 9);
# this is used to generate unix date
my (%months1) = ("Jan" => 0, "Feb" => 1, "Mar" => 2, "Apr" => 3, "May" => 4, "Jun" => 5, "Jul" => 6, "Aug" => 7, "Sep" => 8, "Oct" => 9, "Nov" => 10,"Dec" => 11);
my ($time);
$date =~ s/,|\.//g; # delete any commas or periods that might be in there
$date =~ s/\W/-/g; # change any separators into hyphens
if ((defined($months{lc(substr($date,2,3))})) && (length($date) == 9)) { # date format ddMmmyyyy
$day = substr($date,0,2);
$month = $months{lc(substr($date,2,3))};
$year = substr($date,-4);
}
elsif ((defined($months{lc(substr($date,1,3))})) && (length($date) == 8)) { # date format dMmmyyyy
$day = substr($date,0,1);
$month = $months{lc(substr($date,1,3))};
$year = substr($date,-4);
}
elsif ((substr($date,4,2) > 12) && (length($date) == 8)) { # date format ddmmyyyy
$year = substr($date,4,4);
$month = substr($date,2,2);
$day = substr($date,0,2);
}
elsif (($date > 19000000) && (length($date) == 8)) { # date format yyyymmdd
$year = substr($date,0,4);
$month = substr($date,4,2);
$day = substr($date,6,2);
}
else {
$date = lc($date);
@date_part = split /-/,$date;
$year = $date_part[2];
$year = int($year);
if ($year<100) {
if ($year<20) {
$year += 2000;
}
else {
$year += 1900;
}
}
if (defined($months{$date_part[1]})) { #date format dd-Mmm-yyyy
$day = $date_part[0];
$month = $months{$date_part[1]};
}
elsif (defined($months{$date_part[0]})) { #date format Mmm-dd-yyyy
$day = $date_part[1];
$month = $months{$date_part[0]};
}
elsif ($date_part[0] > 12) { #date format dd-mm-yyyy
$day = $date_part[0];
$month=$date_part[1];
}
elsif ($date_part[1] > 12) { #date format mm-dd-yyyy
$day = $date_part[1];
$month=$date_part[0];
}
elsif ($american_dates) { #ambiguous date -- American format
$day = int($date_part[1]);
$month=int($date_part[0]);
}
else { #ambiguous date -- the rest of the world
$day = int($date_part[0]);
$month=int($date_part[1]);
}
}
unless ($day and $month and $year) { return undef; }

use Time::Local;
eval {
$day = int($day); $year = int($year) - 1900;
$month=$month-1;
$time = timelocal($sec,$min,$hour,$day, $month, $year);
};
if ($@) { return undef; } # Could return 0 if you want.
return ($time);
}
sub get_computed_date {
# --------------------------------------------------------
# Returns the date in the format "dd-mmm-yyyy hh:mm:ss".
# If you have changed your date format in sub get_date, you should also change it here.
my ($time) = @_;
($time) || ($time = time());
my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime($time);
my (@months) = qw!Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec!;
($day < 10) and ($day = "0$day");
$year = $year + 1900;
($sec < 10) and ($sec = "0$sec");
($min < 10) and ($min = "0$min");
($hour < 10) and ($hour = "0$hour");
return "$day-$months[$mon]-$year $hour:$min:$sec";

}
sub get_date {
# --------------------------------------------------------
# Returns the date in the format "dd-mmm-yyyy hh:mm:ss".
#######################################################################################
# #
# Warning: If you change the default format, you must also modify the &date_to_unix #
# subroutine which converts your date format into a unix time in seconds for sorting #
# purposes. #
# #
# If you have changed the date format in sub date_to_unix, be sure to make appropriate#
# changes here. The structure of the date must be identical between sub date_to_unix #
# and sub get_date. #
# #
#######################################################################################
my ($time) = @_;
($time) || ($time = time());
my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime($time);
my (@months) = qw!Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec!;
($day < 10) and ($day = "0$day");
$year = $year + 1900;
($sec < 10) and ($sec = "0$sec");
($min < 10) and ($min = "0$min");
($hour < 10) and ($hour = "0$hour");
return "$day-$months[$mon]-$year $hour:$min:$sec";
}

Well, I finally found the catch to this mod.... sub get_date used to output a human readable number that was the year month day. But now it outputs real unix time (ie the date plus the time = unix time). Problem is you can't represent dates before 1970 jan 1. I found this when I couldn't input my birthdate. It's not a problem for me, I just changed the field type to alpha, but wanted to make you all aware of this small gotcha.
Wes