Difference between dates

htmanning has asked for the wisdom of the Perl Monks concerning the following question:

Monks, I have a dateadded field and a lastlogin field in this format 2014-04-15, in a mysql database. I'm trying to subtract one from the other so I can show all records since the lastlogin but not having any luck. Can someone help?

Thanks!

UPDATE: I found it. This works:

SELECT *
FROM order_details
WHERE order_date BETWEEN CAST('2014-02-01' AS DATE) AND CAST('2014-02-
+28' AS DATE);
[download]

Comment on Difference between dates Download Code

Replies are listed 'Best First'.
Re: Difference between dates by davido (Cardinal) on Apr 20, 2015 at 04:49 UTC
Can't this be solved almost entirely within an SQL query? Dave	[reply]
Re^2: Difference between dates by aaron_baugher (Curate) on Apr 20, 2015 at 07:56 UTC
Yes. The OP's code doesn't seem to match his question, since it doesn't do any subtracting, but MySQL can give you the difference between two dates: `SELECT id, DATEDIFF(lastlogin, dateadded) FROM order_details;` [download] Aaron B. Available for small or large Perl jobs and *nix system administration; see my home node.	[reply] [d/l]
Re: Difference between dates by shmem (Chancellor) on Apr 20, 2015 at 06:57 UTC
UPDATE: I found it. This works: Good. Just for reference, to convert '2014-04-15' to a UNIX timestamp: `use Date::Parse; $time = str2time('2014-04-15'); print scalar localtime $time,"\n"; __END__ Tue Apr 15 00:00:00 2014` [download] perl -le'print map{pack c,($-++?1:13)+ord}split//,ESEL'	[reply] [d/l]
Re: Difference between dates by dasgar (Priest) on Apr 20, 2015 at 04:33 UTC
You might need to parse your date strings to use this, but Date::Calc should work.	[reply]
Re: Difference between dates by chacham (Prior) on Apr 20, 2015 at 14:52 UTC
A couple comments on the SQL itself: using SELECT * outside of an EXISTS() clause or ad hoc query can cause bugs and confusion. You can bug-proof and self-document your code by specifying which columns you want returned. BETWEEN is inclusive. So, unless want to includes those dates as well, you might need to specify another date or use +/-1.	[reply]