So I was minding my own business trying to manually insert an entry in the user table for the XMPP server that I am using when I got an error when I tried to set the creation date. Turns out that the creation date column in the user table is a 15 character string, 0 padded, that represents the number of milliseconds since January 1st, 1970. Why it is not a standard date type is beyond me but I digress.
Well if you ever need to do such a thing, and who doesn’t, here is the SQL query to do it:
select lpad(round(extract(epoch from now())*1000),15,0) as result from dual;
result
-------------------------
001144246603582
Note that this query is specific to Postgres. You may have to alter a few things to get it to work with whatever database you are using.
It is also worth noting that extracting the epoch from the date only returns the number of seconds since January 1, 1970. To get milliseconds we need to multiply by 1000.
How odd. Did the authors give any indication why they needed such fine grained times? I would have thought the day would suffice.
I agree that it is odd. I just expected it to be a standard date type so you can imagine my surprise when I got the error.
According to the documentation this is because “date column type support varies widely across databases. Therefore, Wildfire specially encodes dates as VARCHAR values. Each date is a Java long value which is 0-padded to 15 characters.”
The database schema documentation is here.