Web Development

Ask something
Discussion Locked

Convert BIGINT to DATE format

0 helpful
3 replies

by joko.santoso

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi..

I have a mySQL query like the following:

SELECT wo.CREATEDTIME FROM wo

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.

3 replies

by gsquared

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.

by tony hopkinson

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

This discussion is closed to new replies.