lastlogin is getting saved as time() in my mysql table and it is varchar mode.

querying the database to send out emails to remind my users that they have not logged in from past 30 days

SELECT * FROM members WHERE lastlogin = DATE_SUB(CURDATE(), INTERVAL 30 DAY)

i tried with several options, but not able to query exactly as i need. for example,

SELECT * FROM members WHERE lastlogin < DATE_SUB(CURDATE(), INTERVAL 30 DAY)

SELECT * FROM members WHERE lastlogin < DATE_ADD(CURDATE(), INTERVAL 30 DAY)

can some one help me on this to achieve this function please.

Member Avatar for diafol

In which format are you storing your dates?

update the table with .time().

Member Avatar for diafol

so unix timestamp in that case? date_sub/add use the Y-m-d H:i:s format. You can't compare apples and oranges.

BTW - if storing unix timestamp - why are you using varchar? Should be INT

this script is bought from the market place, hard to understand their coding, hence seeking help out here. Can you state me an example if you dont mind

Member Avatar for diafol
SELECT * FROM members WHERE DATE_ADD(FROM_UNIXTIME(lastlogin), INTERVAL 30 DAY) < CURDATE()

not tested

thanks for your great help, its working perfectly fine. thanks so much.

just need to understand the query,

DATE_ADD(FROM_UNIXTIME(lastlogin), INTERVAL 30 DAY)

: this adds +30 day interval and then it finds the member who is less than (CURDATE()) current date login.

please correct me if am wrong.

Or is it had to be

DATE_SUB if i have to send the mail who has not been logged in since 30 days.

Member Avatar for diafol

date_add or date_sub doesn't matter - can use either with > / < or change places of lastlogin and CURDATE()

if solved, mark it so with the link below

sure, i will play around with it and a great thanks to you for helping me to solve this

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.