Moved to: WoodyHayday.com
Woody Hayday

Fresh Ideas

Hi there! This is my old blog, I don't hang around here much.
You can now find me trying to mix things up here:
Visit WoodyHayday.com Subscribe To My Email List

MySQL datetime examples – first/last day of the year – dates and unix-times

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.

This entry was posted in Snippets, SQL Server, Transactional SQL, Web Development, Web Technology and tagged , , , , , , , , . Bookmark the permalink. Both comments and trackbacks are currently closed.

2 Comments

  1. Andrejs
    Posted January 21, 2014 at 3:40 pm

    Alternative:

    SELECT SUBDATE(MAKEDATE(YEAR(CURDATE()), 1), INTERVAL 1 DAY)

  2. Posted January 24, 2014 at 1:55 pm

    Cheers Andrejs 🙂

Woody Hayday

Comments Archive

Hi there. This is my old blog and it's archived, so you can no longer post new comments on this post (MySQL datetime examples – first/last day of the year – dates and unix-times).

Read my new blog about writing software and stories at WoodyHayday.com

The New Blog
WoodyHayday.com
A Quote..
"Give me the fruitful error any time, full of seeds, bursting with its own corrections. You can keep your sterile truth for yourself."
Vilfredo Pareto
Old Random Projects
    © Woody Hayday 2008-2017