How are dates stored in the DB?

[DBowsky]

Hi,

Working up some custom reporting for my Cerb4 install. Need to know what format dates are stored within:

ticket.created_date and ticket.updated_date.

Having trouble converting them to mm/dd/yy or other formats using MySQL. The very few that actually display as dates using standard MySQL queries (most of them are invalid, returning NULL) are in the year 2012.

Thanks!

[Hildy]

They're stored as epoch timestamps. Use select from_unixtime(date) as real_date... to view them.

[jstanden]

It's worth noting they're epochs based on GMT too.

In MySQL you can use these functions:

Turn a YYYY-MM-DD date into an epochal timestamp:

SELECT UNIX_TIMESTAMP(NOW());
Turn an epochal timestamp into a YYYY-MM-DD date:

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()));


We use GMT epochs because they make a helpdesk portable between timezones and it's much easier to do date arithmetic.

[DBowsky]

Thanks guys.