Monitoring, Management & Location Tracking

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]

Example:
# 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) . "
"'

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
http://www.postgresql.org/docs/8.1/interactive/functions-formatting.html

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):
> SELECT 
> mac, 
> 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