Jump to content

Archived

This topic is now archived and is closed to further replies.

Moron

Date calculation help, please?

Recommended Posts

I want to take a result from my query:

[code]$RESULT[EPEDAT] (Pay period ending date)[/code]

...and compare it to a variable that gives me the UNIX timestamp of two weeks ago:

[code]$twoweeks = strtotime('-14 days');[/code]

I want to only display the most current paystub, based on $RESULT[EPEDAT] from the database.

I've tried converting $RESULT[EPEDAT] to a timestamp and then doing the math, but I can't seem to make it work.

The record date ($RESULT[EPEDAT]) is in mm/dd/yyyy format.

What's the best way to go about this?

Share this post


Link to post
Share on other sites
with the format you have it stored in, how did you go about changing it to a UNIX timestamp? you'd have to do a little more than just strtotime on it. try something like this, and then do your comparison:
[code]
<?php
$date = "02/14/2006";
list($month, $day, $year) = explode("/", $date);

// now make your timestamp
$ts = mktime(0,0,0,$month,$day,$year);
?>
[/code]

hope this helps

Share this post


Link to post
Share on other sites
[quote author=obsidian link=topic=109419.msg440965#msg440965 date=1159195742]
with the format you have it stored in, how did you go about changing it to a UNIX timestamp? you'd have to do a little more than just strtotime on it. try something like this, and then do your comparison:
[code]
<?php
$date = "02/14/2006";
list($month, $day, $year) = explode("/", $date);

// now make your timestamp
$ts = mktime(0,0,0,$month,$day,$year);
?>
[/code]

hope this helps
[/quote]

Thanks, but I goofed. The date from the query, $RESULT[EPEDAT], has no slashes. It's stored as 9252006 and so on, so there shouldn't be any parsing necessary.

Share this post


Link to post
Share on other sites
[quote author=Moron link=topic=109419.msg440972#msg440972 date=1159196410]
Thanks, but I goofed. The date from the query, $RESULT[EPEDAT], has no slashes. It's stored as 9252006 and so on, so there shouldn't be any parsing necessary.
[/quote]

Unfortunately, you've chosen a format that just made life difficult for you. Without knowing how those 'dates' are put together, I'd just guess that:

year is the last four characters of the 'date'
day is the first two characters of the last six characters of the 'date'
month is the first two characters if the 'date' is eight characters long, otherwise it's the first character of the 'date' when the 'date' is seven characters long.

Is that correct? If so, then substr() will be able to produce the day, month, and year from the string.

Share this post


Link to post
Share on other sites
actually, there will still be parsing necessary. think of it this way:
Sept 25, 2006 = "9252006"
Oct 12, 2001 = "10122001"

with that naming convention, Oct 12, 2001 is actually after Sept 25, 2006... make sense?

Share this post


Link to post
Share on other sites
Okay, now let me drop the [b]really[/b] stupid question:

I want the query to select the most current record, based on date.

Is there a way to do that without a lot of timestamp functions and math?

Share this post


Link to post
Share on other sites
Let me run this by you:

I now have a variable:
[code]$recordyear = $RESULT[EPEDAT];[/code]

...and a substr statement:
[code]echo substr("$recordyear", 3, 8); [/code]

This gives me "2005," (no quotes) which is the year of the paystub it's pulling, 10122005.

I could do the same technique for the month and day, BUT.... how do I use this in the query? $RESULT[EPEDAT] doesn't exist intil the query has been ran.

Share this post


Link to post
Share on other sites
you'll need to familiarize yourself with the mysql SUBSTRING function as well. you can use results of string functions as part of your WHERE clause to do what you're after.

Share this post


Link to post
Share on other sites
[quote author=obsidian link=topic=109419.msg441104#msg441104 date=1159204495]
you'll need to familiarize yourself with the mysql SUBSTRING function as well. you can use results of string functions as part of your WHERE clause to do what you're after.
[/quote]

I've tried this in the WHERE clause, but how can it work if the substr variable to extract the year doesn't exist until after the query has been run?

Share this post


Link to post
Share on other sites
[quote author=Moron link=topic=109419.msg441106#msg441106 date=1159204725]
I've tried this in the WHERE clause, but how can it work if the substr variable to extract the year doesn't exist until after the query has been run?
[/quote]

read my post carefully. i'm suggesting you use the [b][i]MySQL[/i][/b] substring. check it out in the manual along with [url=http://dev.mysql.com/doc/refman/5.1/en/string-functions.html]all the other string functions[/url] that may help you out.

Share this post


Link to post
Share on other sites
[quote author=obsidian link=topic=109419.msg441111#msg441111 date=1159205049]
[quote author=Moron link=topic=109419.msg441106#msg441106 date=1159204725]
I've tried this in the WHERE clause, but how can it work if the substr variable to extract the year doesn't exist until after the query has been run?
[/quote]

read my post carefully. i'm suggesting you use the [b][i]MySQL[/i][/b] substring. check it out in the manual along with [url=http://dev.mysql.com/doc/refman/5.1/en/string-functions.html]all the other string functions[/url] that may help you out.
[/quote]

Thanks. Will explore.

Share this post


Link to post
Share on other sites
Good luck with substrings ... because the length of your strings is not always the same ... unless you [i]always[/i] have a two-digit day number and a two-digit month number.

Share this post


Link to post
Share on other sites
[quote author=AndyB link=topic=109419.msg441219#msg441219 date=1159212239]
Good luck with substrings ... because the length of your strings is not always the same ... unless you [i]always[/i] have a two-digit day number and a two-digit month number.
[/quote]

that's what i thought, too, but after no concern seemed to be given over my last post, i figured i'd drop it. i thought maybe i missed something :P

Share this post


Link to post
Share on other sites
Well, gents....  :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter?

Share this post


Link to post
Share on other sites
[quote author=Moron link=topic=109419.msg441260#msg441260 date=1159215400]
Well, gents....  :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter?
[/quote]

how about days? 1 vs. 11???

Share this post


Link to post
Share on other sites
[quote author=obsidian link=topic=109419.msg441262#msg441262 date=1159215726]
[quote author=Moron link=topic=109419.msg441260#msg441260 date=1159215400]
Well, gents....  :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter?
[/quote]

how about days? 1 vs. 11???
[/quote]

Nope. Days less than 10 are preceeded by a zero, like 9042006 for September 4th.

Share this post


Link to post
Share on other sites
[quote author=Moron link=topic=109419.msg441289#msg441289 date=1159217457]
Nope. Days less than 10 are preceeded by a zero, like 9042006 for September 4th.
[/quote]

well, i guess then you could always simply check the length of the string before you run substring on it. that way, you'll know whether to grab the first one or two digits. otherwise, i'm not sure of a good way to substring [i]from the right[/i]

Share this post


Link to post
Share on other sites
[quote author=Moron link=topic=109419.msg441260#msg441260 date=1159215400]
Well, gents....  :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter?
[/quote]

That would be reply #3 in this thread.

At the risk of this being deemed spam (since I've said it so many times) ... why don't you convert your database information to 'real' dates instead of these alternatives that require all sorts of mathematical and logical gymnastics?

Share this post


Link to post
Share on other sites
[quote author=AndyB link=topic=109419.msg441358#msg441358 date=1159225776]
[quote author=Moron link=topic=109419.msg441260#msg441260 date=1159215400]
Well, gents....  :o.....in answer to AndyB's point, the month can be either one or two digits (September is just 9, October is, of course, 10). But...can't I count from the right side and solve this matter?
[/quote]

That would be reply #3 in this thread.

At the risk of this being deemed spam (since I've said it so many times) ... why don't you convert your database information to 'real' dates instead of these alternatives that require all sorts of mathematical and logical gymnastics?
[/quote]

Thanks, Andy. That would be my first choice, as well, but I've been told that [i]"the database is what it is and you'll have to work with what you have."[/i] I think the reason is because there are other scripts pointing to it and they don't want to rock the boat, which I can understand.

Share this post


Link to post
Share on other sites
So do you use still that 8-track player?

I understand the problems with legacy software and database data.  My company made a lot of money in  resolving or working around Y2K problems that were also a legacy problem.  But just because that's how great-great-grandfather did it doesn't mean we have to keep doing it the same way whenever we have a [i]new[/i] problem to deal with - otherwise we'd still be using quill pens and parchment to record all the data.  You know how much of a headache it has been to deal with the legacy data when a 'real' date would have made your recent problems trivial ... and the effort in conversion would have paid for itself many times over (unless you're working for free).

How about adding a new field to the database that can contain the date in an unmistakable, usable, format.  All future applications should use that and ignore the wacko, pre-historic notions of date; all old applications can continue to be a monument to the past.  Run a simple script 'offline' to generate a real date from each group of pseudo-date data and write it to the new date field.  21st century scripts are simpler and the historical stuff can continue until ... whatever.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.