Jump to content

Date calculation help, please?


Moron

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?

Link to comment
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
Link to comment
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.
Link to comment
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.

Link to comment
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.
Link to comment
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?
Link to comment
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.
Link to comment
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.
Link to comment
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
Link to comment
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???
Link to comment
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.
Link to comment
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]
Link to comment
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?
Link to comment
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.
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.