Bug #75558 Feature Request: Stardate function
Submitted: 19 Jan 2015 20:09 Modified: 28 Apr 2016 18:35
Reporter: Gillian Gunson (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S4 (Feature request)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: date, FUNCTION, stardate

[19 Jan 2015 20:09] Gillian Gunson 
Description:
This is a feature request for a date function to return the Stardate of a given date, based on the specifications given in the 2009 Star Trek movie: http://en.wikipedia.org/wiki/Stardate#Star_Trek_.282009.29

For example, today (January 19, 2015) would be Stardate 2015.19.

Note this can be calculated using a stored function, but an embedded date function as part of the server code would allow MySQL to be Enterprise-ready.

How to repeat:
mysql> select stardate(now());
+-----------------+
| stardate(now()) |
+-----------------+
| 2015.19         |
+-----------------+
1 row in set (0.01 sec)
[20 Jan 2015 9:33] Mark Leith 
DROP FUNCTION IF EXISTS stardate;

DELIMITER $$

CREATE DEFINER='root'@'localhost' FUNCTION stardate (in_date DATETIME)
    RETURNS TEXT
    SQL SECURITY INVOKER
    DETERMINISTIC
    NO SQL
BEGIN
    RETURN CONCAT(YEAR(in_date), '.', DAYOFYEAR(in_date));
END$$

DELIMITER ;
[20 Jan 2015 13:11] MySQL Verification Team 
Hello Gillian,

Thank you for the feature request!

Thanks,
Umesh
[12 Feb 2015 19:10] Federico Razzoli 
Maybe it could be implemented as a timezone. This would avoid frequent conversions from/to UTC, which are quite annoying for enterprise users.
[28 Apr 2016 18:35] Gillian Gunson 
The stardate should probably be in PST given that Starfleet HQ is in San Francisco.