Translating UTC (epoch) time / date to human-readable format
Timestamps used in database records are often in UTC* format, This is a value equal to the number of seconds since midnight January 1, 1970 --not something that is easy for most of us to convert in our heads. Below are several ways to convert these values into human readable format.
*UTC = Coordinated Universal Time (also known as UNIX epoch time)
*** ON THE AMP COMMAND LINE ***
Paste the timestamp into the date command as follows:
# date -d @[timestamp]
# date -d @1272036516
Fri Apr 23 08:28:36 PDT 2010
*** USING AN EPOCH TIME CONVERTER ***
- Go to: http://www.epochconverter.com/
- Paste the timestamp into the text field next to the "TIMESTAMP TO HUMAN DATE" button.
- Click on the button to convert.
*** USING COMMAND LINE PERL ***
# perl -e 'print scalar localtime(1186500499) . "\n"'
Tue Aug 7 08:28:19 2007
*** IN DATABASE COMMANDS ***
You can use the to_timestamp() function in database functions to convert UTC to human readable format.
For a detailed description of this function and its formatting options refer to:
PostgreSQL Documentation: 9.8. Data Type Formatting Functions
Here's an example database command that uses the to_timestamp() and date_trunc() functions...
To find all disconnected users for a given day (in this case 2010-05-20):
> TO_TIMESTAMP(connect_time) AS connect_time
> FROM client_historical
> WHERE DATE_TRUNC('day', TO_TIMESTAMP(connect_time)) = DATE_TRUNC('day', timestamp '2010-05-20')
> ORDER BY mac, connect_time
. . . . . . . mac . . . . . . | . . . connect_time
00:14:A4:D1:36:62 | 2010-05-20 10:45:19-07
00:17:C4:6C:25:F1 | 2010-05-20 01:02:28-07
00:17:C4:6C:25:F1 | 2010-05-20 05:52:48-07