General discussion


Convert BIGINT to DATE format

By joko.santoso ·

I have a mySQL query like the following:


The result is supposed to be a list of date in a human-readable date format (i.e. 26 Jul 2006, 13:40:03) instead of a BIGINT value (i.e. 1153896003593).

Pls advise how to convert the BIGINT value into a human-readable date format. Tks.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by gsquared In reply to Convert BIGINT to DATE fo ...

I'm not familiar with mySQL, but that looks like it's probably the number of base time units counted from the database base date.

For example, if the database counts dates in milliseconds, you would add that number of milliseconds to whatever the base date is for mySQL. (For MS-SQL, it's 1 Jan 1753.)

T-SQL has a dateadd function for that. MySQL probably has either the same or something similar.

Collapse -

depends on what that number represents

by Tony Hopkinson In reply to Convert BIGINT to DATE fo ...

If it was datetime and it had the same base date then there are afew ways of converting it, which ones are available though depends on your version of MySQL

Easiest way to find out is to to pull up the mysql help files (download from their site if you haven't got them) and search fior data arithmetic.
Check the available from version comments though in the help, saves you a lot of typing to get a syntax error.

If the base date is different you'll had to do a bit of arithmetic, but tha should be fairly simple

Collapse -

Based on the size of the number...

by TechExec2 In reply to Convert BIGINT to DATE fo ...

Based on the size of the number, it is likely a DATETIME type (1). You probably want to use the DATE_FORMAT() function to format it (2).

Post back and let us know what you did.


(1) MySQL Data Type Storage Requirements

(2) MySQL Date and time functions

Related Discussions

Related Forums