Woody Hayday

For some reason I had an abstract memory of SQL SERVER 2000, timestamps and pain. Who knows why. Perhaps its a faulty memory. lol. Anyhow working with timestamps in MySQL/MSSQL these days is nothing short of simple. With MySQL Workbench free its ridiculous how far the barrier of entry for database design has come, its now all pleasant schema’s and error messages that make sense, gone are the days of spending half the time fixing/navigating the database system, they just work now (99% of the time) what a lucky generation of programmers! Anyhow here’s some datetime examples that you might find useful:

Get last day of last year – obvious, but still (+1 this for first day):

SELECT DATE(CURDATE()- INTERVAL DAYOFYEAR(CURDATE()) DAY);

And how useful… FROM_UNIXTIME takes a unix timestamp and makes it a friendly datetime, its like they KNOW I am coding for facebook data acquisition…Anyhow…

To make a GMT UNIX time-stamp for the first day of this month:

SELECT UNIX_TIMESTAMP(CONVERT_TZ(DATE(CURDATE()- INTERVAL DAYOFMONTH(CURDATE()) DAY), '+0:00', 'SYSTEM'));

You can switch out the dates obviously, and there are probably other ways, still, mostly pain free.