Redshift convert integer to Timestamp type and convert epoch time to timestamp on the fly

Currently, Redshift does not support to_timestamp function, which is convenient for converting String or Integer into Timestamp format. Below is a quick notes on how to convert String or integer to be timestamp in Redshift.
Simple, using substring function to get parts for Year, Month, Date, Hour, Minute, Second from String or Integer and concatenate them into Timestamp format and cast to timestamp. Below are examples,
Suppose Table t1 has a column (col1) have integer value in pattern YYMMDDhhmm. For example, it value is 0403051030. It means Year 2014, March fifth, and thirty minutes past ten o'clock. We can use below two methods to convert it to be Timestamp value.

SELECT 
cast(('20' || substring(col1,1, 2) || substring(col1,3, 2) || 
      substring(col1,5, 2) || ' ' || substring(col1,7, 2) || ':' 
      || substring(col1,9, 2)) as timestamp) 
FROM t1;

Or

SELECT 
cast(to_date(col1, 'YYMMDD') || ' ' 
    || substring(col1,7,2) || ':' 
|| substring(col1,9,2) as timestamp) 
FROM t1;

Also, get idea from a blog about how to convert epoch time to timestamp on fly in Redshift.

SELECT  
    (TIMESTAMP 'epoch' + myunixtime * INTERVAL '1 Second ') 
AS   mytimestamp 
FROM  
    example_table

why I can not see all EBS size after spin a EC2 instance

Suppose you newly spin an EC2 instance from an AMI image that 8GB ESB volume and you set up new EC2 instance to have larger volume as 100GB. However, you will see only 8GB size when you use command like df or parted. Below are example commands to determine.

[root@ ~]# file -s /dev/xvde
/dev/xvde: Linux rev 1.0 ext4 filesystem data (needs journal recovery) (extents) (large files) (huge files)
[root@0 ~]# file -s /dev/sda
/dev/sda: symbolic link to `xvde'

[root@ ~]# df
Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/xvde        8256952 1749732   6087792  23% /
tmpfs            7619396       0   7619396   0% /dev/shm
[root@ip-10-75-130-120 ~]# df -BG
Filesystem     1G-blocks  Used Available Use% Mounted on
/dev/xvde             8G    2G        6G  23% /
tmpfs                 8G    0G        8G   0% /dev/shm

(parted) unit kb                                                          
(parted) print free                                                       
Model: Xen Virtual Block Device (xvd)
Disk /dev/xvde: 107374182kB
Sector size (logical/physical): 512B/512B
Partition Table: loop

Number  Start   End          Size         File system  Flags
 1      0.00kB  107374182kB  107374182kB  ext4

(parted) unit gb                                                          
(parted) print free                                                       
Model: Xen Virtual Block Device (xvd)
Disk /dev/xvde: 107GB
Sector size (logical/physical): 512B/512B
Partition Table: loop

Number  Start   End    Size   File system  Flags
 1      0.00GB  107GB  107GB  ext4


[root@ip-10-75-130-120 ~]# cat /etc/fstab 
/dev/xvde  /        ext4      defaults         0 0
devpts     /dev/pts  devpts  gid=5,mode=620   0 0
tmpfs      /dev/shm  tmpfs   defaults         0 0
proc       /proc     proc    defaults         0 0
sysfs      /sys      sysfs   defaults         0 0

Solution is very simple: run command resize2fs!

[root@ip-10-33-53-115 ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvde       7.9G  1.7G  5.9G  23% /
tmpfs           7.3G     0  7.3G   0% /dev/shm
[root@ip-10-33-53-115 ~]# resize2fs /dev/xvde
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/xvde is mounted on /; on-line resizing required
old desc_blocks = 1, new_desc_blocks = 7
Performing an on-line resize of /dev/xvde to 26214400 (4k) blocks.
The filesystem on /dev/xvde is now 26214400 blocks long.

[root@ip-10-33-53-115 ~]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvde        99G  1.7G   92G   2% /
tmpfs           7.3G     0  7.3G   0% /dev/shm
[root@ip-10-33-53-115 ~]#