CODEWITHSUNDEEP
mysqldatetime
user2202898
user2202898

Reputation: 3

Working with a non-standard date time format in MySQL

I have a system that auto-populates a mysql database with data. Unfortunately the the only time value is being stored in the format YYYYMMDDHHMMSS.SSS. Have a script that runs against the data in the database in a 30 minute cron to alert on certain data trends. The problem that I'm having is that the data in the database goes back 7 days. I only want the script to run against the past 30 minutes of data.

I tried using something like:

SELECT foo FROM table WHERE StartTime >= DATE_SUB(NOW(), INTERVAL 30 MINUTES);

Unfortunately this didn't work, I'm assuming because the stored time formation does not match a standard SQL Timestamp format.

Can anyone suggest an alternate method? Thanks.

Upvotes: 0

Views: 117

Answers (1)

Vatev
Vatev

Reputation: 7590

StartTime >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 30 MINUTE),'%Y%m%d%H%i%s.000')

Upvotes: 1

Related Questions