Converting a Long into a Date/Time (MySQL)

In MySQL, sometimes people store timestamps as long values (time since epoch, etc.)

In order to turn that into a date, you need to do the following in MySQL:

SELECT FROM_UNIXTIME(event_time) FROM MY_TABLE

or if you like a date nicely formatted, then:

SELECT DATE_FORMAT(FROM_UNIXTIME(event_time), '%d/%m/%y %h:%i%p') FROM MY_TABLE

NB: This assumes that your event_time is in seconds. If you are using Java, for example, then you will have probably stored your timestamp in millisconds (as kicked out by System,currentTimeMillis() ) so in this instance, MySQL will rather rudely just return NULL.

To fix this, simply divide by 1000 first as follows:

SELECT FROM_UNIXTIME(event_time/1000) FROM MY_TABLE

or if you like a date nicely formatted, then:

SELECT DATE_FORMAT(FROM_UNIXTIME(event_time/1000), '%d/%m/%y %h:%i%p') FROM MY_TABLE

Making NOT NULL columns nullable again in Firebird

I came across the need to make a NOT NULL column nullable again recently, and was somewhat suprised to find that it's actually a little harder than you might think in Firebird SQL.

take the following ficticious example:

CREATE TABLE pointless
(
    widget VARCHAR(40) NOT NULL
);

now, in, say Oracle, I would simply do this:

ALTER TABLE pointless MODIFY COLUMN widget VARCHAR(40);

et voila !!

Turns out you can't do that in Firebird, so I had to do a little digging. It turns out that Firebird internally handles NOT NULL columns as constraints, and you have to drop the constraint to remove the not null clause.

The full explanation of what to do, and why is here: http://www.firebirdsql.org/manual/nullguide-alter-pop-tables.html#nullguide-make-column-nullable but for those of you masses that just can't wait, try this:

SELECT rc.rdb$constraint_name
FROM rdb$relation_constraints rc
JOIN rdb$check_constraints cc
ON rc.rdb$constraint_name = cc.rdb$constraint_name
WHERE rc.rdb$constraint_type = 'NOT NULL'
AND rc.rdb$relation_name = 'tablename'
AND cc.rdb$trigger_name = 'fieldname'

Substituting the table and column names as appropriate. The resulting row will give you the internal constraint name that you can simply drop as follows:

ALTER TABLE tablename DROP CONSTRAINT constraintname

Job done !