Datum & Tijd (MySQL)

Time stamp & date stamp

select date_format(now(),"%Y%m%d_%H%i");
20180724_1124

select date_format(now(),"%Y%m%d_%H%i%s");
20200115_143829

select date_format(now(),"%Y.%m.%d");
2020.01.15

select now();						-- 2017-04-28 17:17:02
select date_format(now(),"%m");         		-- 04
select date_format(now(),'%m');         		-- 04
select date_format(now(),"%d");         		-- 28
select date_format(now(),"%m/%d/%y");   		-- 04/28/17
select date_format(now(),"%m/%d/%Y");   		-- 04/28/2017
select date_format(now(),"%m-%d-%Y");   		-- 04-28/2017
select date_format(now(),"%m/%d/%Y")+50;   		-- 54 → Dit werkt dus niet
select date_format(now()+50,"%m/%d/%Y");		-- Null
select now()+interval 50 day;				-- 2017-06-17 17:18:53
select date_format(now()+interval 90 day,"%m/%d/%Y");	-- 07/27/2017

Unix Time - from_unixtime()

Met from_unixtime() kun je Unix tijdwaardes leesbaar formatteren. Bv.:

select from_unixtime(1453991397);   # Output: 2016-01-28 15:29:57

Unix Time - unix_timestamp()

Met unix_timestamp() converteer je een datum of tijd in een leesbaar formaat terug naar Unix Time:

select unix_timestamp(now());                   # Output: 1676745678
select unix_timestamp("Oct. 19, 2015");         # Output: 0
select unix_timestamp("2015-10-19");            # Output: 1445205600
select unix_timestamp("2011-07-11 07:50:51");   # Output: 1310363451
select unix_timestamp("2011-07-11");            # Output: 1310335200
select unix_timestamp("2011-07");               # Output: 0

See also

• Unix Epoch Time

Bronnen

• https://en.wikipedia.org/wiki/Unix_time
• https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
• http://stackoverflow.com/questions/10763031/how-to-subtract-30-days-from-the-current-datetime-in-mysql
• https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add