Member Avatar for sammry

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.

  • 2 Contributors
  • 8 Replies
  • 187 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by sammry

Dani AI

Generated

Good summary and fix from — the root problem is mixing formats. stored epoch seconds as text, then tried to compare those strings to MySQL DATE expressions. Two reliable approaches:

  • Convert the column to a proper numeric epoch (INT UNSIGNED) and compare numeric cutoffs (fast, index-friendly).
  • Or migrate the column to a DATETIME in UTC and use date arithmetic on DATETIME values.

A safe, minimal migration plan (run on a backup or during a maintenance window):

ALTER TABLE members MODIFY lastlogin INT UNSIGNED NOT NULL DEFAULT 0;
UPDATE members SET lastlogin = CAST(lastlogin AS UNSIGNED);
ALTER TABLE members ADD INDEX (lastlogin);

Once the column is an integer you can compute the cutoff in your application and query by numeric comparison (this preserves the index and scales). Example PHP workflow (PDO shown for safety):

$cutoff = time() - 30*24*60*60; // 30 days in seconds
$stmt = $pdo->prepare('SELECT id, email FROM members WHERE lastlogin < :cutoff');
$stmt->execute([':cutoff' => $cutoff]);

Additional practical tips:

  • Do not wrap the column in functions in WHERE clauses — that prevents index use. Compute the cutoff on the app side instead.
  • Add a separate column (e.g., last_reminder_sent) so you can avoid repeat emails and track when a reminder was sent.
  • Batch results (LIMIT/offset or cursor) and log deliveries to avoid overloading the mail server.
  • Test the migration on a copy, check for non-numeric lastlogin values before converting, and keep timestamps in UTC to avoid timezone drift.

These changes will make the reminder query simple, efficient, and reliable for large user tables.

Recommended Answers

All 8 Replies

Member Avatar for Member #120589

In which format are you storing your dates?

Member Avatar for sammry

update the table with .time().

Member Avatar for Member #120589

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

Edited by Member #120589 because: n/a
Member Avatar for sammry

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 Member #120589 
SELECT * FROM members WHERE DATE_ADD(FROM_UNIXTIME(lastlogin), INTERVAL 30 DAY) < CURDATE()

not tested

Member Avatar for sammry

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.

Edited by sammry because: missed a query
Member Avatar for Member #120589

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

Member Avatar for sammry

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.